text string

  • Thread starter Thread starter TONY
  • Start date Start date
T

TONY

I have a column in an excel sheet (max 65000 rows) which contains text. I
want to strip off the first word of the text string and place this into a
different column leaving the rest(minus this one word) in the original
column cell. in toal i have 85000 records and doing this by hand will take
for ever. Once this is done I will transfer all records to an access
database.

can some one please give me a guide on how to go about doing this
 
this should find the space and delete the first word
Sub stripfirstword()
For Each c In Selection
c.Value = Right(c, Len(c) - InStr(1, c, " "))
Next
End Sub

or to let excel do for all in col A.
Sub stripfirstword()
For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
c.Value = Right(c, Len(c) - InStr(1, c, " "))
Next
End Sub
 
many thanks don.

Tony
Don Guillett said:
this should find the space and delete the first word
Sub stripfirstword()
For Each c In Selection
c.Value = Right(c, Len(c) - InStr(1, c, " "))
Next
End Sub

or to let excel do for all in col A.
Sub stripfirstword()
For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
c.Value = Right(c, Len(c) - InStr(1, c, " "))
Next
End Sub
Click to expand...
 
If your data starts in A1, then a couple equations can split the input into
the first word (in B1) and everthing else (in C1). In B1:
=IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1)), and in C1:
=IF(ISERROR(FIND(" ",A1)),"",TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))). Autofill
those formulas through columns B & C. Then copy / paste special values to
replace the formulas with their results. (It sounds like you may not need B1
at all, but that's your call).
 
Back
Top