# Formula =Left and =Right Help

1. ## Formula =Left and =Right Help

Hello,

This is my first post here.

Hopefully the problem I'm having has a fairly simple solution !

Essentially we currently use the formula below to return the required text:

=LEFT(H2,FIND(":",H2)-1)

Cell H2 Contains: 165-3935: BOLT-HEX HEA

Text returned: 165-3935

However, due to a change in what our cells now contain the format is now:

Cell H2 now Contains: STRAP,CAT,261-6797

Desired Text to return: 261-6797

I am just unsure of the correct formula to return the desired text in the most simple way.
smc4312

2. ## Re: Formula =Left and =Right Help

how consistent are the locations? if it is always 8 spaces in from the right a simple right will accomplish that.

3. ## Re: Formula =Left and =Right Help

maybe this... =RIGHT(H2,SEARCH(",",H2)+2)

4. ## Re: Formula =Left and =Right Help

Originally Posted by Sam Capricci
how consistent are the locations? if it is always 8 spaces in from the right a simple right will accomplish that.
Unfortunately the desired text is not always the same length. For example - 1W-2644 or 574-4543 as below.

SUPPORT ASSEMBLY,CAT,1W-2644
LIFTER ASSEMBLY,VA,CAT,574-4543

5. ## Re: Formula =Left and =Right Help

Originally Posted by Sam Capricci
maybe this... =RIGHT(H2,SEARCH(",",H2)+2)
Thanks, but unfortunately i received an error. I will try attaching the excel sheet to this forum post.

6. ## Re: Formula =Left and =Right Help

Maybe this:

=TRIM(RIGHT(SUBSTITUTE(H2,",",REPT(" ",99)),99))

7. ## Re: Formula =Left and =Right Help

Maybe this:

=TRIM(RIGHT(SUBSTITUTE(H2,",",REPT(" ",99)),99))
Wow, thanks so much that worked perfectly.

There are currently 1 users browsing this thread. (0 members and 1 guests)