Pull Characters Around a Certain Character

[nguyenr85]

So I'm trying to pull data from a set. The info I need is the numbers around a decimal.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Have[/TD]
[TD]Need[/TD]
[/TR]
[TR]
[TD]Intel Core 2.14 GHz[/TD]
[TD]2.14[/TD]
[/TR]
[TR]
[TD]AMD Core 1.99 GHz[/TD]
[TD]1.99[/TD]
[/TR]
[TR]
[TD]Intel Pentium 2.56 GHz[/TD]
[TD]2.56[/TD]
[/TR]
</tbody>[/TABLE]


I have been using the formula =MID(A1,FIND(".",A1,1),3) so I can get the numbers after the decimal but how can I get JUST the number before? Using LEFT function will not work because it just pulls from the beginning.

 

[PaddyD]

Hi - welcome to the board.

Assuming that (a) there's only one decimal in the string to worry about, and (b) processor power only goes up to 9:

=MID(A1,FIND(".",A1,1)-1,4)

...should do it.

 

[jtakw]

Hi,

If what you want is Always a single digit before decimal, and 2 digits after:

Excel 2010
	A	B	C
1	Intel Core 2.14 GHz		2.14
2	AMD Core 1.99 GHz		1.99
3	Intel Pentium 2.56 GHz		2.56
Sheet6

Cell Formulas
Range	Formula
C1	=MID(A1,FIND(".",A1)-1,4)

C1 formula copied down.

 

[hiker95]

nguyenr85,

Welcome to the MrExcel forum.

Try the following (the results will be numeric, not, text):

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))+0

 

[nguyenr85]

Thanks for the reply and the welcome. It worked! I was missing the -1 in my formula. It makes sense now that I see it. Thanks again!

 

[hiker95]

nguyenr85,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.