Converting Text to numbers & Extracting a Portion from l

[mianmazher]

In cell A1 I have a text entry as 12.55,12,13,14.555

I want to extarct the only the last portion ie 14.555

If I change of A1 to 12.55,12,13 the result should be 13

if I change the vale to 12.55,12,13,14.555,100.5555 the result should be 100.5555


Plz help

Thanks in advance

 

[Aladin Akyurek]

Re: Converting Text to numbers & Extracting a Portion fr

=IF(A1<>"",--RIGHT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1,",",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))),"")

 

[Richie(UK)]

Re: Converting Text to numbers & Extracting a Portion fr

Hi,

OK, I can't take credit for this formula (it's taken from one of John Walkenbach's web pages) but it does what you need:

Book1.xls
	A	B	C	D
1	12.55,12,13,14.555	14.555
Sheet1

In case the formula doesn't fully show its:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))

Of course, depending on your needs you could just use 'Text To Columns' from the Data menu and use a comma as the delimiter.

HTH

 

[mianmazher]

Re: Converting Text to numbers & Extracting a Portion fr

Thanks Aldadin and Richie for solving the problem
Now what if I want to average the numbers entered

A1=12.5,12.55,15,13
B2=average 13.2625

if A1=12,12.55
B2= average 12.275


Thanks in advance excel experts.

 

[Richie(UK)]

Re: Converting Text to numbers & Extracting a Portion fr

Hi,

The simplest approach would be to use 'Text To Columns' to split out the values and then just use the normal AVERAGE function.

HTH

 

[Todd Bardoni]

Re: Converting Text to numbers & Extracting a Portion fr

Richie, this has already been suggested to the OP and we seem to be repeating ourselves.

http://www.mrexcel.com/board2/viewtopic.php?t=75507

 

[mianmazher]

Re: Converting Text to numbers & Extracting a Portion fr

Richie I want to do this with the help of the formula.

I hav elong list of entries , which keep on increasing ever hour.

Is this be done by formula.

Once again Thanks.

 

[Aladin Akyurek]

Re: Converting Text to numbers & Extracting a Portion fr

Richie I want to do this with the help of the formula.

I hav elong list of entries , which keep on increasing ever hour.

Is this be done by formula.

Once again Thanks.

Then you need to download and install the morefunc.xll add-in or search this site for EVAL in VBA.

Book5
	A	B	C	D
1	12.5,12.55,15,13	13.2625
2	12,12.55	12.275
3
Sheet2

The formula in B1 is:

=AVERAGE(INDEX(EVAL("{"&A1&"}"),0,0))