text string
 

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

text string
 

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
--
Don Guillett
SalesAid Software

"TONY" <STEELE_ANTHONY at HOTMAIL.COM wrote in message
. ..
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

text string
 

many thanks don.

Tony
"Don Guillett" wrote in message
...
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
--
Don Guillett
SalesAid Software

"TONY" <STEELE_ANTHONY at HOTMAIL.COM wrote in message
. ..
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

text string
 

glad to help

--
Don Guillett
SalesAid Software

"TONY" <STEELE_ANTHONY at HOTMAIL.COM wrote in message
. ..
many thanks don.

Tony
"Don Guillett" wrote in message
...
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
--
Don Guillett
SalesAid Software

"TONY" <STEELE_ANTHONY at HOTMAIL.COM wrote in message
. ..
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

text string
 

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

"TONY" wrote:

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

text string
 

Hi Tony,
A macro solution would avoid the messy clean up afterwards
associated with worksheet formulas, if you want a permanent separation.
Rearranging Data in Columns
Separate first word (term) from remainder of cell (#septerm)
http://www.mvps.org/dmcritchie/excel/join.htm#septerm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"TONY" wrote
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.