Solved How do I pull partial data from a cell?

Microsoft corporation Office 365 small b...
December 14, 2017 at 13:07:12
Specs: Windows 10
I am trying to pull data from a cell and combine it elsewhere. I know how to do the combining but it's getting the needed data that's tricky. The column is a bunch of item numbers and they are different lengths.

For instance:

      Column A                              Column B (What I need)                   
MCI-2143 - 2184729                         MCI-2143
MCI-3215-XL - 3847292                      MCI-3215-XL
MCI-4122-BLK-M - 4187435                   MCI-4122-BLK-M

The item numbers sometimes have size and color data in them. They are always one "word" though and are followed by " - " if that will help a function find the data I need.
From a programming perspective I want to pull all data prior to the first space before the hyphen.

message edited by Mark_C


See More: How do I pull partial data from a cell?

Report •

#1
December 14, 2017 at 14:26:44
✔ Best Answer
So . . .
=LEFT(A1, SEARCH(" ",A1)) 

How To Ask Questions The Smart Way


Report •

#2
December 14, 2017 at 14:55:49
How did I not know Search was a function?!?!
That's perfect and exactly what I needed!
Thanks!

Report •

#3
December 14, 2017 at 18:02:48
Is it perfect?

I can't tell if the results you want have a trailing space or not.

As written, Razor2.3's suggestion is going to include the space. That may or may not be an issue, only you can tell us that.

If you don't want the trailing space, then just subtract 1 from the value returned by the SEARCH function:

=LEFT(A1, SEARCH(" ",A1)-1)

BTW, the FIND function will also work. FIND is case sensitive, SEARCH is not. However, a "space" has no case, so both functions will work for your application.

=LEFT(A6,FIND(" ",A6)-1)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
December 14, 2017 at 20:14:54
Yeah, I might have Googled, "excel tokenize string," and grabbed the first line of code off of the first hit. I did wonder if it'd cause a trailing space, but then I'd have to open Excel, and that'd more than double the time I spent getting my answer.

How To Ask Questions The Smart Way


Report •

#5
December 15, 2017 at 06:30:02
It did cause the space, but once I understood how the search function was working, I used the "-1" as you did in your example DerbyDad03 to get what I needed.
Still solved my problem quickly and saved me hours of work!

Report •

#6
December 15, 2017 at 07:01:10
Glad to hear it! Come on back if you have any other questions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question