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

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

Hi Kaj,

Am Wed, 4 Mar 2015 21:24:50 GMT schrieb :

Cell A1: Car, black 66,00 52,80
The word I want to extract is "Car, black"

or with builtin functions:
=LEFT(A1,FIND("#",SUBSTITUTE(A1," ","#",2))-1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

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

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

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

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

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

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

Hi Garry,

Am Thu, 05 Mar 2015 03:32:27 -0500 schrieb GS:

Wow! I totally missed the double space!

you need new glasses also ;-)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Might be better to revise criteria to delimit at the " "!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Thu, 05 Mar 2015 03:32:27 -0500 schrieb GS:

Wow! I totally missed the double space!

you need new glasses also ;-)


Regards
Claus B.

I don't wear glasses, but perhaps I should!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Claus and Maurizio

Thank you very much for all of your proposals.

This works to my full satisfaction: =LEFT(A1,FIND(" ",A1)-1) [it also
works even if I don't add "-1"]
I start counting spaces from the right (Car, black 66,00 52,80)

However, now I can't figure out the formula if I want to extract the amount
after 1 space from the right, in this case "66,00"
Can you help once again?

Best regards,
Kaj Pedersen

---
Denne e-mail blev kontrolleret for virusser af Avast antivirussoftware.
http://www.avast.com

Try...

Function ParseInstrRev2(rng As Range)
' Returns 1st word left of " "
ParseInstrRev2 = Split(rng, " ")(0)
End Function

Function ParseInstrRev1(rng As Range)
' Returns 1st value left of last " "
Dim vTmp
vTmp = Split(rng, " ")
'Convert text to currency
ParseInstrRev1 = CCur(Split(vTmp(1), " ")(0))
End Function

Function ParseInstrRev0(rng As Range)
' Returns last value
Dim vTmp
vTmp = Split(rng, " ")
'Convert text to currency
ParseInstrRev0 = CCur(Split(vTmp(1), " ")(1))
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi,

Am Thu, 5 Mar 2015 09:33:36 GMT schrieb :

However, now I can't figure out the formula if I want to extract the amount
after 1 space from the right, in this case "66,00"
Can you help once again?

try:
=--MID(A1,FIND(" ",A1)+2,5)
The double minus at start are to change text to number


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

Thank you. It works perfectly.

Kaj Pedersen



On 5-Mar-2015, Claus Busch wrote:

try:
=--MID(A1,FIND(" ",A1)+2,5)
The double minus at start are to change text to number

---
Denne e-mail blev kontrolleret for virusser af Avast antivirussoftware.
http://www.avast.com

Hi Garry,

Thank you for your proposal. I have not yet tested but I'm sure it works :-)
If not, I will revert.

Kaj Pedersen

---
Denne e-mail blev kontrolleret for virusser af Avast antivirussoftware.
http://www.avast.com

Hi Kaj,

Am Thu, 5 Mar 2015 13:39:44 GMT schrieb :

Thank you. It works perfectly.

you are welcome.
If you want all parts of your string in a seperate cell use Data = Text
to columns


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Garry, now tested and works to my full satisfaction.
Also very good description you provided. Thank you.
I will keep it for ever in my archive of good Excel tips :-)

Kaj Pedersen

---
Denne e-mail blev kontrolleret for virusser af Avast antivirussoftware.
http://www.avast.com

Garry, now tested and works to my full satisfaction.
Also very good description you provided. Thank you.
I will keep it for ever in my archive of good Excel tips :-)

Kaj Pedersen

---
Denne e-mail blev kontrolleret for virusser af Avast
antivirussoftware. http://www.avast.com

Glad to help! I appreciate the feedback.

FWIW
Claus' suggestion to use DataTextToCols is a good way to parse into
contiguous cols. Using the UDFs allows parsing to any location of your
sheet[s], meaning that on Sheet2 for example...

=ParseInstrRev2(Sheet1!A1)

...also may prove to be of some benefit!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion