Find everything after numbers at the start of a string

[anakaine]

Hi all, I need to identify the qualitative part of a string, and leave the number at the start behind. This may get a little bit more complicated where things have been typed as "1 1/2" for example

For example

Original	Result
1 Cup water	Cup Water
1 1/2 Carrots	Carrots
1/2 cup cilantro or 1/2 cup mint leaf	cup cilantro or 1/2 cup mint leaf
1 egg yolk	egg yolk

[AliGW]

I don't understand this result:

1/2 cup cilantro or 1/2 cup mint leaf > cup cilantro or 1/2 cup mint leaf

Why do you not want to remove the second 1/2?

[anakaine]

In this instance it would be fine to remove the second 1/2.
There may be others where it may not be ok to remove the second number. Eg 2 carrots cut in 1/2

[oeldere]

There a differant approaches.

This is one of them

a1=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))

[AliGW]

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

This only removes everything up to and including the first space character.

There may be others where it may not be ok to remove the second number. Eg 2 carrots cut in 1/2

Which makes it awkward, as I would not know an unnecessary 1/2 from a necessary one.

Presumably these strings can start with numbers other than 1?

[oeldere]

I changed the formula from Find into Search (translationproblem)

a1=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))

Which makes it awkward, as I would not know an unnecessary 1/2 from a necessary one.

@AliGW Off course that is a good point, but for now the OP was satisfied with this one.

[AliGW]

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))

Which has the same result - it only removes everything up to and including the first space, so if you have 1 1/2 carrots you end up with 1/2 carrots.

@AliGW Off course that is a good point, but for now the OP was satisfied with this one.

Do you mean satisfied with leaving the second 1/2 in the middle of the text string? If so, then yes, I agree. If you are saying that the OP is satisfied with your formula, then I wonder why, for the reason stated above.

[oeldere]

Do you mean satisfied with leaving the second 1/2 in the middle of the text string? If so, then yes, I agree.

of course it is this one.

[anakaine]

Thanks for the feedback all.

So the formula supplied doesn't appear to work with the example of "1 1/2 Carrots" as it will just pick all after the first space.

As it turns out my data set needs a huge amount of work as there are random spaces in some examples I didn't supply. Eg 4 -5 carrots. The formula supplied will get me much of the way there, however, so thank you for that.

[Phuocam]

Try this ...

=REPLACE(A1,1,MIN(IFERROR(SEARCH(CHAR(ROW($97:$122)),A1),""))-1,"")

Enter with Ctrl+Shift+Enter.