| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract word after 3 spaces
Hi,
How can I extract a word from a cell when the word I want to extract is always 2 spaces away starting counting spaces from the right. Example: Cell A1: Car, black 66,00 52,80 The word I want to extract is "Car, black" Formula to be used in cell A2? Or perhaps a macro? Thank you in advance. Kaj Pedersen --- Denne e-mail blev kontrolleret for virusser af Avast antivirussoftware. http://www.avast.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Hi,
How can I extract a word from a cell when the word I want to extract is always 2 spaces away starting counting spaces from the right. Example: Cell A1: Car, black 66,00 52,80 The word I want to extract is "Car, black" Formula to be used in cell A2? Or perhaps a macro? Thank you in advance. Kaj Pedersen --- Denne e-mail blev kontrolleret for virusser af Avast antivirussoftware. http://www.avast.com Perhaps a UDF... Function ParseInstrRev2$(rng As Range) Dim vTmp vTmp = Split(rng, " ") ParseInstrRev2 = vTmp(UBound(vTmp) - 2) End Function ...and use as follows in the cell to receive the result... =ParseInstrRev2(A1) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Il giorno mercoledì 4 marzo 2015 22:24:57 UTC+1, ha scritto:
How can I extract a word from a cell when the word I want to extract is always 2 spaces away starting counting spaces from the right. Example: Cell A1: Car, black 66,00 52,80 The word I want to extract is "Car, black" Formula to be used in cell A2? Or perhaps a macro? Hi, =LEFT(A1,FIND(" ",A1)-1) -OR- =LEFT(A1,FIND(CHAR(32)&CHAR(32),A1)-1) -- Ciao! Maurizio |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Ciao Maurizio,
Am Wed, 4 Mar 2015 23:22:32 -0800 (PST) schrieb Maurizio Borrelli: =LEFT(A1,FIND(" ",A1)-1) oh yes. I need new glasses. I did not realize the *2spaces* Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Il giorno giovedì 5 marzo 2015 08:33:04 UTC+1, Claus Busch ha scritto:
Am Wed, 4 Mar 2015 23:22:32 -0800 (PST) schrieb Maurizio Borrelli: =LEFT(A1,FIND(" ",A1)-1) oh yes. I need new glasses. I did not realize the *2spaces* :-D -- Ciao! Maurizio |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Ciao Maurizio,
Am Wed, 4 Mar 2015 23:22:32 -0800 (PST) schrieb Maurizio Borrelli: =LEFT(A1,FIND(" ",A1)-1) oh yes. I need new glasses. I did not realize the *2spaces* Regards Claus B. Now what if there really are 3 spaces as the topic title suggests... car,black whitewalls 60.00 52.80 ...where the correct result becomes "whitewalls" if persisting the 'always 2 spaces' criteria? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Hi Garry,
Am Thu, 05 Mar 2015 03:15:32 -0500 schrieb GS: Now what if there really are 3 spaces as the topic title suggests... I guess the OP counts the spaces from left to right. car,black whitewalls 60.00 52.80 car, black whitewalls 60.00 52.80 ..where the correct result becomes "whitewalls" if persisting the 'always 2 spaces' criteria? Find(" "; A1) finds two connected spaces and gives the correct result if always are two spaces between the text and the numbers. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Hi Garry,
Am Thu, 05 Mar 2015 03:15:32 -0500 schrieb GS: Now what if there really are 3 spaces as the topic title suggests... I guess the OP counts the spaces from left to right. car,black whitewalls 60.00 52.80 car, black whitewalls 60.00 52.80 ..where the correct result becomes "whitewalls" if persisting the 'always 2 spaces' criteria? Find(" "; A1) finds two connected spaces and gives the correct result if always are two spaces between the text and the numbers. Regards Claus B. Wow! I totally missed the double space! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract word after 3 spaces
Il giorno giovedì 5 marzo 2015 09:15:43 UTC+1, GS ha scritto:
Am Wed, 4 Mar 2015 23:22:32 -0800 (PST) schrieb Maurizio Borrelli: =LEFT(A1,FIND(" ",A1)-1) oh yes. I need new glasses. I did not realize the *2spaces* Now what if there really are 3 spaces as the topic title suggests... car,black whitewalls 60.00 52.80 ..where the correct result becomes "whitewalls" if persisting the 'always 2 spaces' criteria? Hi. Word(s?) after or before 3 spaces!? Only OP can decide. :-) =LEFT(A1,FIND(CHAR(32)&CHAR(32)&CHAR(32),A1)-1) -OR- =MID(A4,FIND(CHAR(32)&CHAR(32)&CHAR(32),A4)+3,LEN( A4)) -- Ciao! Maurizio |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract text between two spaces | Excel Discussion (Misc queries) | |||
| Extract all text without spaces and special characters | Excel Worksheet Functions | |||
| how do I reduce the spaces between letters in a word? | Excel Discussion (Misc queries) | |||
| How to remove spaces from a word in a cell | Excel Discussion (Misc queries) | |||
| how do i extract a number from a cell with no spaces? | Excel Worksheet Functions | |||
Hybrid Mode
