Attached is a file that shows external raw data and my desired outcome. It appears there is a tab between in the two. I have researched this, tried Alt+0009 and =Left but I cannot get the result I'm looking for. In 20+ years of using Excel I've never seen this. Can anyone help ?
I copied and pasted the character from the formula bar into this formula:
=LEFT(A2,FIND(" ",A2)-1)
In other words:
=LEFT(A2,FIND("copy_pasted_character",A2)-1)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
If my understanding is correct, I had no problem with this...
=LEFT(A2,SEARCH("Ocean",A2)-1)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
OK based on what you said, as well as what Ali suggested, perhaps I didnt get what you wanted from your sample file?
You how you want the text before "ocean", that has nothing to do with any spaces?
That worked.... Thank you so much for putting an end to the past few hours of my frustration.... what on earth is "ocean" ?
You're welcome.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
It is the text after what you wanted extracted...Originally Posted by M12NIX
OCF OCEAN FRONT JR OCF DOME OCEAN FRONT DOM DOME SWIM OCEANFRONT SWIM SWIM
huh that came out different to what it looked like in the file.
My suggestion still pulled out what you waned
I am beginning to wonder if my suggestion was missed ...
Because there is a tab character in there, which is what I used in my solution.huh that came out different to what it looked like in the file
Who's suggestion did you use?
Thank you for your time Ali.
Here's mine again in the attachment.
Whose solution did you use? Ford's will work as long as every entry has "ocean" in it. Mine uses the rogue character instead.
I used yours FDibbins. Ali's makes total sense but I do not know who to add a tab to a formula. I would like to, however. My intent is to add this to my VBA coding but that's for another thread.
I was just thinking that. I will have to use this time and time again. Ford's is a short term solution to this specific issue but yours will help long term. I'm simply at a loss as to how you added the 'tab' into the formula.
I told you how in post #2.
=LEFT(A2,FIND(" ",A2)-1) (this did not work)
In other words:
=LEFT(A2,FIND("copy_pasted_character",A2)-1) (And I'm sorry, I didn't understand the cpc reference.
Yes, it DOES work - look at the attachment to post #12.
Excel 2016 (Windows) 32 bit
Raw Data Desired Result AliGW OCF OCEAN FRONT JR OCF OCF DOME OCEAN FRONT DOM DOME DOME SWIM OCEANFRONT SWIM SWIM SWIM
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
=LEFT(A2,FIND("character_copied_&_pasted_from_the_formula_bar",A2)-1)
Sheet: Sheet1
I am not sure what you don't understand about copy and paste: with A2 selected, I copied the space character between OCF and OCEAN from the formula bar and pasted it into my formula betwen the speech marks ("").
Last edited by AliGW; 06-22-2020 at 01:35 AM.
My apologies. I think I was overly tired as this morning it makes complete sense. Thank you for your time Ali.
Glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks