trim string before the space - macro request.

[missy22]

I am writing to seek help, in creating a simple macro which can trim string in column A, by starting from the right-side and trimming the anything before the first space and displaying the output (string before the space - starting from the right-side) in column B.

I have attached an example file below, showing the input string in sheet1 and desired output is shown in sheet2 of the file.
test_1.xls

Please note:My actual dataset is more than 200,000 records.

Many thanks for your time and help.

[nathansav]

You need to do something like this

Please Login or Register  to view this content.

not in excel at mo, so not tested.

You can do this as formula, using MID and FIND

Please Login or Register  to view this content.

[wenqq3]

Simple way that no need macro.
1. Text to column
2. Delimited
3. Choose "SPACE"
4. Column data format = TEXT for all
5. You will get multi column.
6. Delete the column you dont want.
7. Combine again all column like =A1 & " " & B1 & " " & C1
8. Drag down to 200000++ records.

[wenqq3]

=LEFT(A1,LEN(A1)-LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))) <--Wrong

Another way by function.

Edit, this is correct

=IF( ISERROR( FIND(" ",A1) ),A1,RIGHT(A1, LEN(A1)-FIND("~", SUBSTITUTE(A1," ","~", LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

[cs454]

Hi missy, I have attached a working document. test_1.xls

HTH

[AB33]

The output is on sheet1 column B, but can be changed if you want it on separate sheet.