Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: How to manipulate text within cells 3

Status
Not open for further replies.

natwod

Programmer
Nov 21, 2002
41
US
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
 



Hi,

No need for VBA!!!

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


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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 ...
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top