Excel: How to manipulate text within cells 3

[natwod]

I am quite new to Excel Macros; I am used to using Word Macros to manipulate tables, and it seems Excel is quite different.


I am trying to 'cut' the last part of the text in a cell and paste it into the adjacent cell, but cannot find much help on this anywhere. It seems to be a fairly basic operation, but without knowing syntax and methods, I am at a loss.

I want to repeat this task for a long range of rows.

For instance, a cell has the value "05/09/08 590.25" and I want to move the "590.25" to the cell to the right, and preferably delete the two spaces (" ").

Thanks a ton for your help!
-N

 

[SkipVought]

Hi,

No need for VBA!!!

Data > Test to columns -- DELIMITED -- SPACE Could take you all of 10 seconds.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SmrRyan]

Another way you might do this is with text manipulation functions. Supose yor cell value as above is In cell A1, then put the following in cell B1:
=RIGHT(A1,6)

This will give the right hand side six characters from cell A1, which in this case would be 560.25

this will work for you if the numbers to extract are all 6 characters long -- such as for a currency. There's also a LEFT(a1,3) function with obvious parallels.

And of course if have a list to extract from, you can drag the fill handle on cell B1 (after the steps above) down as far as need be ...

 

[ThichTraXanh]

exam:
A1 = 05/09/08 590.25

program:

sub SplitText()
dim varTemp as variant

vartemp = split(range("A1").value," ",-1,vbtextcompare)
range("A1").value = varTemp(0)
range("B1").value = varTemp(1)

end sub

You can loop this, get total rows

 

[ThichTraXanh]

Exam:
sub SplitText
dim varObj, varTemp as variant
dim i,j as integer

i = 1
j = 2

Do While i <= worksheets(1).Usedrange.Rows.Count '<--Total Rows
varTemp = Split(Range("A" & i).Value, " ", -1, vbTextCompare)

for each varObj in varTemp
cell(i,j) = varObj 'Cell B, cell C
j = j+1
next

i = i + 1
Loop

end sub