# Converting Text to numbers & Extracting a Portion from l

#### mianmazher

##### Active Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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,",",""))))),"")

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
ABCD
112.55,12,13,14.55514.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

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

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

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.

Re: Converting Text to numbers & Extracting a Portion fr

mianmazher said:
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
ABCD
112.5,12.55,15,1313.2625
212,12.5512.275
3
Sheet2

The formula in B1 is:

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

Replies
19
Views
438
Replies
8
Views
228
Replies
2
Views
149
Replies
1
Views
120
Replies
6
Views
295

1,206,753
Messages
6,074,743
Members
446,082
Latest member
fgiron83

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back