Formula ? Need to pull first name only from other sheet?

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows
='Time Sheet'!C8

So I use this and it will return Super - Local 14 right.

What I'm looking for it to only pull the first word Super.

Any help thanks.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
This will work for the example that you shared

=LEFT('Time Sheet'!C8,FIND(" ",'Time Sheet'!C8)-1)
 
Last edited:

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows
Perfect only issue I ran into if the cell has one word. It wont work and return #Value. Can we add something in there to recognize both. If Super alone. Return Super and if Super - Local 14 or whatever etcccc retun First word Super?

Thanks
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Perfect only issue I ran into if the cell has one word. It wont work and return #Value. Can we add something in there to recognize both. If Super alone. Return Super and if Super - Local 14 or whatever etcccc retun First word Super?

Thanks

The formula that I posted returns everything to the left of the first space. If there is no space, the formula will not work. Can C8 be anything other than "Super -..." or "Super"? If so, you will have to share some more examples so that we can make the formula more robust.

If those are the only two instances, try the following.

=IFERROR(LEFT('Time Sheet'!C8,FIND(" ",'Time Sheet'!C8)-1),"Super")
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi yeah its gonna be all different names so examples I can run into these examples below. I need though to pull in first name only regardless if the name is alone or with anything. Examples

Super = Super
Super Local 14 = Super
Carpenter - Local 15 = Carpenter
Cement Foremen = Cement

So the first formula works fine. I just need it to work also its the word is alone like one word.
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Hi yeah its gonna be all different names so examples I can run into these examples below. I need though to pull in first name only regardless if the name is alone or with anything. Examples

Super = Super
Super Local 14 = Super
Carpenter - Local 15 = Carpenter
Cement Foremen = Cement

So the first formula works fine. I just need it to work also its the word is alone like one word.

In that case, try

=IFERROR(LEFT('Time Sheet'!C8,FIND(" ",'Time Sheet'!C8)-1),'Time Sheet'!C8)
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows
That wroks perfect thanks.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top