=LEFT(A2,FIND(" ",A2)-1)
You may want to add some error handling if there isn't always more than one word.
=IF(ISNUMBER(FIND(" ",A2)-1),LEFT(A2,FIND(" ",A2)-1),A2)
--
David Biddulph
"CrisT" <CrisT@discussions.microsoft.com> wrote in message news:DF295315-D059-4376-9A78-3662D35739DF@microsoft.com...
Hello, I was hoping someone could help me with a formula. I have a column in
a spreadsheet that has the following types of text (for example):
Subaru WRX
Subaru STI
Jeep Wrangler
Jeep Grand Cherokee
What I am looking for is a formula to pull the first word from the column and put it in another column, say column T.
Thank you in advance!
This is great! I've been looking for this... Now, is there a way to extract the last word in a string ONLY if it is not the only word in the string. If it is then i want it to be found in the formula you wrote above. I'm basically trying to extractthree names but not always is there a second or even a third name, for example, Cher. I have it all working but the last name will duplicate the first if it is a one word name. :(
Hello,
The =split( cell, "character") function would do this. You'll just have to make sure that you have enough columns to the right to accommodate this split.
For your example, you would need 3 blank columns to the right available.
Assuming cells are listed from A1:A4, to get the type of car, you'll need to split using a space character (" ") using the formula below.
=split(A1, " ")
This will result in
A1 = Suburu WRX in B1 = split(A1,"") and showing in B1 = Suburu C1=WRX
...
=split(A4, " ")
This will result in
A4 = Jeep Grand Cherokee in B1 = split(A4,"") and showing in B4 = Jeep C4=Grand D4 = Cherokee
I hope this helps!
On Saturday, January 25, 2020 at 7:08:27 AM UTC+8, jackie...@gmail.com wrote:
Hello,
The =split( cell, "character") function would do this. You'll just have to make sure that you have enough columns to the right to accommodate this split.
For your example, you would need 3 blank columns to the right available.
Assuming cells are listed from A1:A4, to get the type of car, you'll need to split using a space character (" ") using the formula below.
=split(A1, " ")
This will result in
A1 = Suburu WRX in B1 = split(A1,"") and showing in B1 = Suburu C1=WRX
...
=split(A4, " ")
This will result in
A4 = Jeep Grand Cherokee in B1 = split(A4,"") and showing in B4 = Jeep C4=Grand D4 = Cherokee
I hope this helps!
My request is pretty much the same but with one tweak:
if it has multiple words, I want the first one. If it is only a one-word string then I just want that one. =LEFT(A2,FIND(" ",A2)-1) does not work on the latter part. Any idea?
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 285 |
Nodes: | 16 (2 / 14) |
Uptime: | 78:15:23 |
Calls: | 6,489 |
Files: | 12,096 |
Messages: | 5,276,466 |