# Left formula to compensate for varied number of characters

1. ## Left formula to compensate for varied number of characters

Hello,

I'm trying to take the first column's values and transfer only the names to the next column of the table below.

Column 1
Mike Trout (LAA - CF)
Jose Altuve (HOU - 2B)
Trea Turner (WSH - SS)
Paul Goldschmidt (ARI - 1B)

Column 2
Mike Trout
Jose Altuve
Trea Turner
Paul Goldschmidt

The left formula will need a way to vary the num char value part of the formula. My first thought was to have it "go" until it reachs a second " ", but I'm not sure if that's even possible in excel. Any suggestions are appreciated.

2. ## Re: Left formula to compensate for varied number of characters

Try this:
``Please Login or Register  to view this content.``
Pete

3. ## Re: Left formula to compensate for varied number of characters

There is no * in the data. Sorry, that's just the way the table posted.

4. ## Re: Left formula to compensate for varied number of characters

Are there any ways to find the second space in a string?

5. ## Re: Left formula to compensate for varied number of characters

Formula:
`Please Login or Register  to view this content.`

6. ## Re: Left formula to compensate for varied number of characters

Hi, to all!

You can try:
=LEFT(A2,FIND("(",A2)-2)

Or, taking care of second space, try:
=LEFT(A2,FIND("|",SUBSTITUTE(A2," ","|",2))-1)
Or:
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),198))

Blessings!

7. ## Re: Left formula to compensate for varied number of characters

the formula =LEFT(A2,FIND("(",A2)-2) worked best in my case.

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