Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate VBA results and an * in a cell
Hi, I would like to concatenate the results of copying a cell and adding an * to left of the number value, in the same cell. For example, it would look like Range("B1").Value = "*" + Range("A1").Value . But it does not work. For sure, trying to mathematically sum a String and a number isn't a good idea in the form I've just written. So I imagine I would have to convert the number into String. How, I'd like too know. But then, if I want to use again the value in B1 (so removing the * in VBA and do maths with those numbers), how could I reverse this process? Thx! Werner -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304 View this thread: http://www.excelforum.com/showthread...hreadid=382935 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate VBA results and an * in a cell
Hi Werner Instead of Range("B1").Value = "*" + Range("A1").Value . Try Range("A1") = Range("B1").Value & Range("A1").Value . (where Range("B1").Value = "*"). The & is the equivalent to Concatenate in VBA. to Remove Later the "*" Use Code: -------------------- Size = Len(Range("A1").Text) - 1 ' The Len function returns the lenght of the string minus 1 to eliminate the * Range("A1") = Right(Range("A1").Value, Size ) 'The right function returns the SIZE characters FROM the right of the string. or in one line: Range("A1") = Right(Range("A1").Value, Len(Range("A1").Text) - 1) -------------------- Check in the Help for a detailed explaination of the Right and Len Functions Hope this helps you Saludos Jose Luis Werner Wrote: Hi, I would like to concatenate the results of copying a cell and adding an * to left of the number value, in the same cell. For example, it would look like Range("B1").Value = "*" + Range("A1").Value . But it does not work. For sure, trying to mathematically sum a String and a number isn't a good idea in the form I've just written. So I imagine I would have to convert the number into String. How, I'd like too know. But then, if I want to use again the value in B1 (so removing the * in VBA and do maths with those numbers), how could I reverse this process? Thx! Werner -- jose luis ------------------------------------------------------------------------ jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312 View this thread: http://www.excelforum.com/showthread...hreadid=382935 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate VBA results and an * in a cell
Thank you Jose Luis for your code. I understand Len and Right correctly with the comments you added in your code. It will help me a lot. :) I have another question in mind. If I want to add in my vba code an If condition that search to know if there's a * just to the left of my number, how could I do that. I imagine it must be something like If Range("B1").Value = "*" & *(something that say there's a number (not a specific one)) Then. Have a good day! Werner -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304 View this thread: http://www.excelforum.com/showthread...hreadid=382935 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate VBA results and an * in a cell
Hi Werner, Thanks by your feedback. In order to find if there is a * in the string use if Left(Range("B1").value,1) = "*" then your code for number with "*" Else your code for number without "*" End if The Left function does the same as Right but from the other side :). Hope this helps, Regards Jose Luis Werner Wrote: Thank you Jose Luis for your code. I understand Len and Right correctl with the comments you added in your code. It will help me a lot. :) I have another question in mind. If I want to add in my vba code an I condition that search to know if there's a * just to the left of m number, how could I do that. I imagine it must be something like I Range("B1").Value = "*" & *(something that say there's a number (not specific one)) Then. Have a good day! Werne -- jose lui ----------------------------------------------------------------------- jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331 View this thread: http://www.excelforum.com/showthread.php?threadid=38293 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate VBA results and an * in a cell
Thanks again Jose, it helped a lot. It was precisely what I wanted t know. I fully understand the way Left and Right functions work now : Have a good day! Werne -- Werne ----------------------------------------------------------------------- Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430 View this thread: http://www.excelforum.com/showthread.php?threadid=38293 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I lookup several values and concatenate the results? | Excel Discussion (Misc queries) | |||
CONCATENATE not producing any results | Excel Worksheet Functions | |||
CONCATENATE not producing any results | Excel Worksheet Functions | |||
Problem with Concatenate - Results are too long for CSV | Excel Worksheet Functions | |||
I can't get my concatenate formula results to show | Excel Discussion (Misc queries) |