[SOLVED] 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

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
[email protected]
"TONY" <STEELE_ANTHONY at HOTMAIL.COM> wrote in message
news:[email protected]...
>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
>

many thanks don.

Tony
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> "TONY" <STEELE_ANTHONY at HOTMAIL.COM> wrote in message
> news:[email protected]...
>>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
>>
>
>

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"TONY" <STEELE_ANTHONY at HOTMAIL.COM> wrote in message
news:[email protected]...
> many thanks don.
>
> Tony
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]
>> "TONY" <STEELE_ANTHONY at HOTMAIL.COM> wrote in message
>> news:[email protected]...
>>>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
>>>
>>
>>
>
>

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

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.