Taking an integer out of a string

Hi guys,

I'm having some trouble taking a second string out of a dropbox I have. I'm able to get the first integer but am not able to get the second.

In my drop box I have something like "Deck -- 32 -- 90" - Indicating the temperature in both C and F.

I currently have:

Please Login or Register  to view this content.

This code extracts the 32, but I can't seem to manipulate it to get the 90 out! Any help would be greatly appreciated!

Originally Posted by jeffreybrown

Hi Mike,

An easier might be text to columns.

Used Fixed width >> Create columns around the numbers >> One the 3rd step of the wizard select the number numbers columns one by one and select Do not import column (skip)

If I understand what you're saying correctly, I'm not sure that'll work because I created a new column that combines multiple cells into one to display a large string for my drop down boxes in order to see all the data associated with that option. I may be misunderstanding your directions (I'm not by my computer thissecond) so I can test it in an hour or so)

Originally Posted by jeffreybrown

Next question, when you extract the 32 and 90, are they going in the same cell, two different cells...?

2 different cells.. That code I copied is from a cell that requires that first 32. (works correctly)

Originally Posted by jeffreybrown

Hi Mike,

To pull the 90...

Try

=--TRIM(RIGHT(SUBSTITUTE(Q24," ",REPT(" ",100)),100))

BTW: This works for me to pull the 32...

=LEFT(REPLACE(Q24,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},Q24&"0123456789"))-1,""),FIND(" ",Q24)-3)

The

Please Login or Register  to view this content.

doesn't seem to be working for me. It's giving me a blank cell.

Originally Posted by jeffreybrown

What does the string look like you are using this on?

Deck -- 32 -- 90

or

"Deck -- 32 -- 90"

Technically it looks like:

Deck -- 32 -- 90(space)

I would just use Right or some other function like that, but there are longer names and sometimes decimal places (doubles) so using right wouldn't be very effective and could cause some more problems.

Hi Mike,

If the last two characters are always the temperature then try:

=RIGHT(Q24,2)*1

If the first temperature (32) is always 2 characters try:

=TRIM(MID(Q24,FIND(" ",Q24,1)+2,3))*1

These both worked for me (also assuming the "--" are actually blanks if not replace the " " with "--").

HTH
Steve

Originally Posted by ChemistB

Try this modification of Jeff's formula

=--TRIM(RIGHT(SUBSTITUTE(TRIM(Q24)," ",REPT(" ",100)),100))

That worked perfectly.

Thank you Jeff, SteveG, and ChemistB.. I appreciate all of your help!

Hi,

This might not be the most elegant way but it worked for me using varying decmial points.

for 32 - =MID(Q24,FIND(" -- ",Q24,1)+4,FIND(" -- ",Q24,FIND(" -- ",Q24,1)+4)-(FIND(" -- ",Q24,1)+4))*1
for 90 - =MID(Q24,FIND(" -- ",Q24,FIND(" -- ",Q24,1)+4)+4,LEN(Q24)-FIND(" -- ",Q24,FIND(" -- ",Q24,1)+4)+4)*1

HTH
Steve