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.
You need to do something like this
not in excel at mo, so not tested.
=mid(strInput,instr(1,strInput,chr(32)),len(strInput)-instr(1,strInput,chr(32)))
You can do this as formula, using MID and FIND
=MID(J24,FIND(" ",J24)+1,(LEN(J24)-FIND(" ",J24)))
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
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.
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
=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," ",""))
Last edited by wenqq3; 09-09-2014 at 04:59 AM.
Hi missy, I have attached a working document. test_1.xls
HTH
The output is on sheet1 column B, but can be changed if you want it on separate sheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks