Excel request

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?

• 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.

I guess you don't want to just put

 =A1&B1

in the third cell?

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.

Danny

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

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

Why do you want to put 11 and 21 together?

Variation:

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.

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

Gasmeterguy

in vba  try this code

a=cell(1,1)*100

b=cells(1,2)

cells(1,4)=a+b

p8

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.

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!

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.

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

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

,

billybouy...