Pull number from middle of text string

[fg2009]

I would like one formula to remove "5206" from this string. I tried a combination of right/left formulas together, but I could not get it to work.

5032.5206.05816.00040501.0000

Thanks!

 

[Willem!]

Hi,

Try:

=MID(TEXT(A1;0);5;4)

your cell is propably formatted as number, that's why it didn't work...

 

[fg2009]

Doesn't work. I tried formating the target cell different ways.

Any ideas?

 

[schielrn]

If you want to remove it you could do something like this, but it still leaves 2 periods together?

=SUBSTITUTE(A1,"5206","")

Sorry I ready it wrong when you said you wanted to pull it from there, Willem!'s formula should work, but maybe you need to replace the ; with commas if you haven't?

=MID(A1,5,4)

Hope that helps.

 

[Willem!]

When I put in a cell (e.g. A1):

<table style="border-collapse: collapse; width: 164pt;" border="0" cellpadding="0" cellspacing="0" width="219"><col style="width: 164pt;" width="219"><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 164pt;" align="right" width="219" height="20">5.032.520.605.816.000.000.000.000</td> </tr></table>
and use this formula:

=MID(TEXT(A1;0);5;4)

I get:

<table style="border-collapse: collapse; width: 48pt;" border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">5206</td> </tr></table>
Perhaps you need to replace the ";" with "," (I use Excel 07)

Does that help?

 

[Willem!]

Darn! sry, you tried to remove the piece of string, not extracting

...it too late te be sharp at this time, thanx schielrn...

 

[fg2009]

Thank you both.

Replacing ; with , works. (I have Excel 03)

Cheers!

FG

 

[Willem!]

Well, glad I could assist!

 

[schielrn]

Thank you both.

Replacing ; with , works. (I have Excel 03)

Cheers!

FG

Just as an FYI... the ; and , differences are international setting and the country version of excel and not the result of the version (i.e. 2003 or 2007).

 

[Willem!]

Just as an FYI... the ; and , differences are international setting and the country version of excel and not the result of the version (i.e. 2003 or 2007).

thanx, just got 07, just found it there was a difference