Pull number from middle of text string

fg2009

Board Regular
Joined
Mar 20, 2009
Messages
59
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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Willem!

Board Regular
Joined
Mar 6, 2009
Messages
139
Hi,

Try:

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

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

fg2009

Board Regular
Joined
Mar 20, 2009
Messages
59
Doesn't work. I tried formating the target cell different ways.

Any ideas?
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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!

Board Regular
Joined
Mar 6, 2009
Messages
139

ADVERTISEMENT

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!

Board Regular
Joined
Mar 6, 2009
Messages
139
Darn! sry, you tried to remove the piece of string, not extracting :biggrin:

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

fg2009

Board Regular
Joined
Mar 20, 2009
Messages
59

ADVERTISEMENT

Thank you both.

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

Cheers!

FG
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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!

Board Regular
Joined
Mar 6, 2009
Messages
139
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 :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,987
Members
414,115
Latest member
SFUser

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top