Data to the left of the first space

[bobgrand]

Hello all,
I have a column of product codes. The beginning part of the code is what we call the prefix. This can be anywhere from 2 to 6 characters in length followed by a space. I need to take the prefix part of the product code and put them in a separate column. I tried a couple different formulas but was not getting the right result.

Any help would be greatly appreciated

Have a great day
Bob

 

[Benders]

Assuming Cell A1 has ABCD 1234. Enter the following in B1

=MID(A1,1,FIND(" ",A1))

This should give you ABCD

 

[bobgrand]

Awesome,

Thank you so much

Have a great day

 

[Jonmo1]

You might want to subtract 1 from the result of Find.
Otherwise you get "ABCD " <-including the space

=MID(A1,1,FIND(" ",A1)-1)

 

[bobgrand]

Hello all,

Many years ago I started this thread and now I need to add to it. Finding the data to the left of the first space has worked like a charm. I am now finding data where there is no space and the result is giving me #VALUE !.

I can't figure out the formula.

If there is no space in the characters in cell A1 put that data in B1 otherwise do this ~~> =MID(A1,1,FIND(" ",A1)-1)

Any help would be greatly appreciated.

Thanks
Bob

 

[RoryA]

You can just add a space to the end of A1 before looking for it:

=LEFT(A1,FIND(" ",A1&" ")-1)

 

[bobgrand]

I don't think just adding a space is an option. There are hundreds of items scattered in different files where the spaces have been removed.

 

[jtakw]

Hi,

Adding the " " (SPACE) in the formula as suggested by Rory is to Avoid the #VALUE error when the Cell the formula is looking at doesn't Already have a Space within the Text String.
If No Space was in the original Text String, the formula will return Whatever is in that cell. Try it.

 

[Rick Rothstein]

I am now finding data where there is no space and the result is giving me #VALUE !.

I can't figure out the formula.
[/COLOR]

If I understand correctly, you have the same values, but the space after the prefix has been removed. If that is correct, what are the prefixes (letters only?) and what comes after them (numbers only?)?

 

[jtakw]

I am now finding data where there is no space and the result is giving me #VALUE !.

If there is no space in the characters in cell A1 put that data in B1 otherwise do this ~~> =MID(A1,1,FIND(" ",A1)-1)

Bob

Hi Rick, I believe if there's no Space, OP just want whatever is in the cell...might be wrong.