Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extract the first word from a cell?
I'm using Excel 2003 and need to return the first eight characters from a cell.
The "Left" function is fine for this but where the first word is less than eight characters I only want to extract the first word and "left" includes the start of the second word! Can anyone help? |
#2
|
|||
|
|||
Hi
try =LEFT(A1,FIND(" ",A1)-1) -- Regards Frank Kabel Frankfurt, Germany Adam Cole wrote: I'm using Excel 2003 and need to return the first eight characters from a cell. The "Left" function is fine for this but where the first word is less than eight characters I only want to extract the first word and "left" includes the start of the second word! Can anyone help? |
#3
|
|||
|
|||
One way:
=LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1),8))) In article , Adam Cole <Adam wrote: I'm using Excel 2003 and need to return the first eight characters from a cell. The "Left" function is fine for this but where the first word is less than eight characters I only want to extract the first word and "left" includes the start of the second word! Can anyone help? |
#4
|
|||
|
|||
Make that:
=LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1)-1,8))) In article , JE McGimpsey wrote: One way: =LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1),8))) |
#5
|
|||
|
|||
Hi J.E. maybe with error checking:
=LEFT(A1,FIND(" ",A1&" ")-1) or =LEFT(A1,MAX(8,FIND(" ",A1&" ")-1)) -- Regards Frank Kabel Frankfurt, Germany JE McGimpsey wrote: Make that: =LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1)-1,8))) In article , JE McGimpsey wrote: One way: =LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1),8))) |
#6
|
|||
|
|||
I like the approach, but neither of those work.
Both, if the first space in the cell is after character 9 (or there is no space) return a too-long string. You could fix the first with =LEFT(A1,FIND(" ",LEFT(A1,8)&" ")-1) The second one can be fixed by using MIN instead of MAX. =LEFT(A1,MIN(8,FIND(" ",A1&" ")-1)) In article , "Frank Kabel" wrote: Hi J.E. maybe with error checking: =LEFT(A1,FIND(" ",A1&" ")-1) or =LEFT(A1,MAX(8,FIND(" ",A1&" ")-1)) |
#7
|
|||
|
|||
Hi J.E.
thanks for the correction. The first approach wans't meant to restrict the output to only 8 characters but only to prevent an error if no space is found. the second one: Shame on me to mess up MIN and MAX :-) -- Regards Frank Kabel Frankfurt, Germany JE McGimpsey wrote: I like the approach, but neither of those work. Both, if the first space in the cell is after character 9 (or there is no space) return a too-long string. You could fix the first with =LEFT(A1,FIND(" ",LEFT(A1,8)&" ")-1) The second one can be fixed by using MIN instead of MAX. =LEFT(A1,MIN(8,FIND(" ",A1&" ")-1)) In article , "Frank Kabel" wrote: Hi J.E. maybe with error checking: =LEFT(A1,FIND(" ",A1&" ")-1) or =LEFT(A1,MAX(8,FIND(" ",A1&" ")-1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I use word wrap in a merged cell in excel 2000? | Excel Discussion (Misc queries) | |||
Extract hyperlink string from excel cell | Links and Linking in Excel | |||
Copy Word table into Excel cell by cell | Excel Discussion (Misc queries) | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
vlookup to extract part cell content | Excel Discussion (Misc queries) |