| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
second word in a string
Hello How Do I extract anything after the first word example column AU
reads Massapequa 5 Prime so in this instance I want to extract 5 prime this is my messed up formula =TRIM(MID(AU4,SEARCH(" ",AU4)+3,255)) I get PRIME . Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
second word in a string
One way.
=MID(A1,FIND(CHAR(32),A1)+1,FIND(CHAR(32),A1,FIND( CHAR(32),A1)+1)-FIND(CHAR(32),A1)-1) If this post helps click Yes --------------- Jacob Skaria "Wanna Learn" wrote: Hello How Do I extract anything after the first word example column AU reads Massapequa 5 Prime so in this instance I want to extract 5 prime this is my messed up formula =TRIM(MID(AU4,SEARCH(" ",AU4)+3,255)) I get PRIME . Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
second word in a string
How about:
=trim(MID(TRIM(Au4),SEARCH(" ",TRIM(Au4),1)+1,255)) The extra trims around AU4 makes sure that there aren't leading/trailing/multiple consecutive internal spaces in what's being searched. Wanna Learn wrote: Hello How Do I extract anything after the first word example column AU reads Massapequa 5 Prime so in this instance I want to extract 5 prime this is my messed up formula =TRIM(MID(AU4,SEARCH(" ",AU4)+3,255)) I get PRIME . Thanks -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
second word in a string
To retrieve from 2nd word onwards you can use
=MID(A1,FIND(CHAR(32),A1)+1,LEN(A1)) The subject line of your post was to extract 2nd word and hence my original response If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: One way. =MID(A1,FIND(CHAR(32),A1)+1,FIND(CHAR(32),A1,FIND( CHAR(32),A1)+1)-FIND(CHAR(32),A1)-1) If this post helps click Yes --------------- Jacob Skaria "Wanna Learn" wrote: Hello How Do I extract anything after the first word example column AU reads Massapequa 5 Prime so in this instance I want to extract 5 prime this is my messed up formula =TRIM(MID(AU4,SEARCH(" ",AU4)+3,255)) I get PRIME . Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return 1st word in string | Excel Discussion (Misc queries) | |||
| Extracting a word from a text string | Excel Discussion (Misc queries) | |||
| Find last word in a string | Excel Discussion (Misc queries) | |||
| choose a particular word in a string | Excel Discussion (Misc queries) | |||
| Word select from a string | Excel Worksheet Functions | |||
Linear Mode
