Extracting text from cell

  • Thread starter Thread starter Bill Fulkerson
  • Start date Start date
B

Bill Fulkerson

Example
In cell A1 you have the following information:

Installation Service [add $20]

I need to take the $20 and copy it to cell A2 for
calculation purposes.

Any help would be great!
 
=RIGHT(A1,FIND(CHAR(32),A1)-1)if your syntax is always the
same placement BUT you have to clean up the ] character.
HTH
 
Or

=--MID(A1,FIND("$",A1),FIND("]",A1)-FIND("$",A1))

since the dollar sign will not cause a value error no need to
dodge the dollar sign in this case

this will work if there is always a $ and a ] (only one instance as well)

--

Regards,

Peo Sjoblom


Gerry Kuta said:
=RIGHT(A1,FIND(CHAR(32),A1)-1)if your syntax is always the
same placement BUT you have to clean up the ] character.
HTH
-----Original Message-----
Example
In cell A1 you have the following information:

Installation Service [add $20]

I need to take the $20 and copy it to cell A2 for
calculation purposes.

Any help would be great!
.
Click to expand...
Click to expand...
 
Isn't there someway that i can tell the formula to look 2
spaces past the $ so it will just grab the $20

-----Original Message-----
Or

=--MID(A1,FIND("$",A1),FIND("]",A1)-FIND("$",A1))

since the dollar sign will not cause a value error no need to
dodge the dollar sign in this case

this will work if there is always a $ and a ] (only one instance as well)

--

Regards,

Peo Sjoblom


Gerry Kuta said:
=RIGHT(A1,FIND(CHAR(32),A1)-1)if your syntax is always the
same placement BUT you have to clean up the ] character.
HTH
-----Original Message-----
Example
In cell A1 you have the following information:

Installation Service [add $20]

I need to take the $20 and copy it to cell A2 for
calculation purposes.

Any help would be great!
.
Click to expand...
Click to expand...


.
Click to expand...
 
That is what it is doing.. What do you get?
I pasted in your example in A1 and the formula
worked for both examples
Are there spaces involved?
Try this adaptation

=--MID(TRIM(A1),FIND("$",TRIM(A1))+1,FIND("]",TRIM(A1))-FIND("$",TRIM(A1))-1
)

or

=--MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom


Bill Fulkerson said:
Isn't there someway that i can tell the formula to look 2
spaces past the $ so it will just grab the $20

-----Original Message-----
Or

=--MID(A1,FIND("$",A1),FIND("]",A1)-FIND("$",A1))

since the dollar sign will not cause a value error no need to
dodge the dollar sign in this case

this will work if there is always a $ and a ] (only one instance as well)

--

Regards,

Peo Sjoblom


Gerry Kuta said:
=RIGHT(A1,FIND(CHAR(32),A1)-1)if your syntax is always the
same placement BUT you have to clean up the ] character.
HTH
-----Original Message-----
Example
In cell A1 you have the following information:

Installation Service [add $20]

I need to take the $20 and copy it to cell A2 for
calculation purposes.

Any help would be great!
.
Click to expand...


.
Click to expand...
Click to expand...
 
ZXW##%% Why don't you use a real example when you post??

=--MID(TRIM(A1),FIND("$",TRIM(A1)),FIND("or",TRIM(A1))-FIND("$",TRIM(A1)))

if there are no other "or" involved

--

Regards,

Peo Sjoblom


Bill Fulkerson said:
This is exactly what i am trying to do

80GB Ultra ATA/100 Hard Drive (7200 RPM) [add $50 or
$2/month1]

extract the $50

120GB Ultra ATA/100 Hard Drive (7200 RPM) [add $80 or
$2/month1]

extract the $80

-----Original Message-----
That is what it is doing.. What do you get?
I pasted in your example in A1 and the formula
worked for both examples
Are there spaces involved?
Try this adaptation

=--MID(TRIM(A1),FIND("$",TRIM(A1))+1,FIND("]",TRIM(A1))- FIND("$",TRIM(A1))-1
)

or

=--MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT
Click to expand...
("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom


Isn't there someway that i can tell the formula to look 2
spaces past the $ so it will just grab the $20


-----Original Message-----
Or

=--MID(A1,FIND("$",A1),FIND("]",A1)-FIND("$",A1))

since the dollar sign will not cause a value error no
need to
dodge the dollar sign in this case

this will work if there is always a $ and a ] (only one
instance as well)

--

Regards,

Peo Sjoblom


=RIGHT(A1,FIND(CHAR(32),A1)-1)if your syntax is always
the
same placement BUT you have to clean up the ]
character.
HTH
-----Original Message-----
Example
In cell A1 you have the following information:

Installation Service [add $20]

I need to take the $20 and copy it to cell A2 for
calculation purposes.

Any help would be great!
.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top