Welcome Guest
Log In | Register )
You last visited January 22, 2017, 11:19 pm
All times shown are
Eastern Time (GMT-5:00)

Excel request

Topic closed. 13 replies. Last post 4 years ago by billybouy.

Page 1 of 1
PrintE-mailLink
Avatar
NASHVILLE, TENN
United States
Member #33372
February 20, 2006
1044 Posts
Online
Posted: July 29, 2012, 2:09 pm - IP Logged

I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".

Is there a method for doing something like this?

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: July 29, 2012, 3:51 pm - IP Logged
    • In VBA you can write a "sub" or a "function".
      You can record a macro and start from there.
    • On the sheet you can use a text function or ampersand ('&').

      Avatar
      NASHVILLE, TENN
      United States
      Member #33372
      February 20, 2006
      1044 Posts
      Online
      Posted: July 29, 2012, 8:35 pm - IP Logged
      • In VBA you can write a "sub" or a "function".
        You can record a macro and start from there.
      • On the sheet you can use a text function or ampersand ('&').

      I tried the "record a macro" but that did not work.

      I am thinking I will need to convert the number to text, then append the two cells.  After appending, I might be able to convert the text back to a number.

      But I am open to other suggestions.

        Avatar
        atlanta,georgia
        United States
        Member #70069
        January 23, 2009
        23 Posts
        Offline
        Posted: July 29, 2012, 8:49 pm - IP Logged

        I guess you don't want to just put

         =A1&B1

        in the third cell?

          dannyboyhouston's avatar - lottery scratch_off_light_shirt-p235645499971128473gyb4_400.jpg
          houston texas
          United States
          Member #130267
          July 10, 2012
          6300 Posts
          Offline
          Posted: July 29, 2012, 9:48 pm - IP Logged

          Hey Gas, you have to go to the cell and format the cell using the Function Wizard next to your input area, when the drop down menue comes up, scroll down until you get to concatenate. You then enter the cell references as such; a1 & b1 make sure there is space between the cell referenec and the '&' mark. Good luck.

          DannyDance

            winsumloosesum's avatar - Lottery-060.jpg
            Pennsylvania
            United States
            Member #2218
            September 1, 2003
            5396 Posts
            Offline
            Posted: July 30, 2012, 1:43 pm - IP Logged

            I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".

            Is there a method for doing something like this?

            Cell A1 = 11

            Cell B1 = 21

            Cell C1 = 1121  formula  =CONCATENATE(A1,B1)  or =VALUE(CONCATENATE(A1,B1))

            If you have pairs that begin with a zero such as 01 through 09 you might want to do the following:

            Right click cell C1. 

            Select "Format Cells"

            Click the "Number" Tab

            In the "Category" box select "Custom"

            To the right under "Type" click the 0 (zero) then add 3 additional zeroes to total 4 zeroes

            Click the OK button


              United States
              Member #124493
              March 14, 2012
              7023 Posts
              Offline
              Posted: July 30, 2012, 2:18 pm - IP Logged

              Main Entry:1con£cat£e£nate Pronunciation:k*n-*ka-t*-n*t, k*n- Function:adjective Etymology:Middle English, from Late Latin concatenatus, past participle of concatenare to link together, from Latin com- + catena chain Date:15th century

               : linked together

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: July 30, 2012, 3:28 pm - IP Logged

                Why do you want to put 11 and 21 together?

                Variation:

                R\CABC
                1120102
                2000000
                311211121

                  Avatar

                  United States
                  Member #130795
                  July 25, 2012
                  80 Posts
                  Offline
                  Posted: July 30, 2012, 6:00 pm - IP Logged

                  GasMeterGuy wrote:  ``I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".  Is there a method for doing something like this?``

                  GasMeterGuy wrote:  ``After appending, I might be able to convert the text back to a number.``

                  The simplest way to accomplish that is:

                  =--(A1 & B1)

                  The double-negative converts numeric text to a number.  Any idempotent arithmetic operation would do the same (e.g. multiply by one).

                  For future reference, Excel questions are best answered in the Microsoft Answers Forum for Office Excel.  Do a Google search for it.  This forum's rules do not permit me to post a web link. Bash

                  (Questions specific to Mac Excel can go into a different forum.  But the operation of the PC and Mac Excels are usually similar enough.)

                    Avatar

                    United States
                    Member #41846
                    June 23, 2006
                    460 Posts
                    Offline
                    Posted: July 31, 2012, 8:28 pm - IP Logged

                    I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".

                    Is there a method for doing something like this?

                    Gasmeterguy

                    in vba  try this code

                    a=cell(1,1)*100

                    b=cells(1,2)

                    cells(1,4)=a+b

                     

                    p8

                      Avatar

                      United States
                      Member #130795
                      July 25, 2012
                      80 Posts
                      Offline
                      Posted: July 31, 2012, 8:51 pm - IP Logged

                      GasMeterGuy wrote (emphasis added):  ``I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".  Is there a method for doing something like this?``

                      Sorry.  I keyed off some other responses and overlooked the fact that you are looking for a VBA solution.  If you want to modify another cell, presumably you are writing this in a sub(routine), not a function.  Then you can write simply:

                      Range("c1") = Range("a1") & Range("b1")

                      And in case you used the Custom format 00 to display a leading zero, if any, you could write:

                      Range("c1") = Range("a1").Text & Range("b1").Text

                      There is no need to convert to a number explicitly.  Excel recognizes the text as a number, just as it does when we enter it manually.  However, if there is any chance that C1 might be formatted as Text beforehand, it would be prudent to write:

                      Range("c1").ClearFormats

                      Range("c1") = Range("a1") & Range("b1")

                      or something similar.

                      VBA also auto-converts the concatenated string to a number when assigning to a VBA numeric variable, e.g:

                      Dim x As Long

                      x = Range("a1") & Range("b1")

                      But if you neglect to assign a type to x (not a good idea) -- that is, it is Variant -- x would become type String in that case.  (Technically, a Variant containing a String.)  Then you could use the double-negative trick, to wit:

                      x = --(Range("a1") & Range("b1"))

                      Again, for future reference, Excel questions are best answered in the Microsoft Answers Forum for Office Excel.

                        SergeM's avatar - slow icon.png
                        Economy class
                        Belgium
                        Member #123700
                        February 27, 2012
                        4035 Posts
                        Offline
                        Posted: August 1, 2012, 12:02 pm - IP Logged

                        I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".

                        Is there a method for doing something like this?

                        Yes, there is!

                          Avatar
                          NASHVILLE, TENN
                          United States
                          Member #33372
                          February 20, 2006
                          1044 Posts
                          Online
                          Posted: August 1, 2012, 8:16 pm - IP Logged

                          I guess you don't want to just put

                           =A1&B1

                          in the third cell?

                          I tried this and it did what I wanted.  Thanks.

                           

                          And the reason I don't want to just put =A1&B1 was due to the fact I was unaware of this function.  Now I have been ed-u-ma-cated.

                            Avatar

                            United Kingdom
                            Member #70630
                            February 7, 2009
                            734 Posts
                            Offline
                            Posted: August 2, 2012, 12:37 pm - IP Logged

                            I have data in cell A1 and B1.  A1 contains the number 11.  B1 contains the number 21.  What I would like to accomplish via VBA is put the numbers of these two cells in another cell such that the third cell would show "1121".

                            Is there a method for doing something like this?

                            Hi GASMETERGUY, here is a book in pdf. file you may find it helpful

                            https://www.box.com/s/097146470666c5ccf69d

                            ,

                            billybouy...

                            Sometimes we can't see the woods for tree's, " so we have to clear a path "