count the number of digits in a cell after the first space

[Vogelmann]

Hi all,

I need a formula that counts the number of digits in a cell, but I am interested only in the specific place within a cell. Lets consider the following case:

00024658 345600 BDD AMSTERDAM

I need to show in the next cell the following set of digits: 345600 - the one that starts right after the first space and ends before the second space. I decided to count the number of digits first, but maybe you know the other way to do that? Thanks a lot in advance!

Karol

[zbor]

Maybe this:

Please Login or Register  to view this content.

[Vogelmann]

thank you, but it doesnt work, I just wanted to add that I am interested in one cell each time - for example, Ive got such sequence: 00024658 345600 BDD AMSTERDAM in cell A1 and I need to have 345600 in cell B1. Hope you know what I mean.

[daddylonglegs]

To get everything between 1st and 2nd space try this formula

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99))

[Vogelmann]

daddylonglegs `this formula contains an error`, and then excel highlights this part of a formula:

A1,"

[Fotis1991]

See ddl's, formula in action...

[Vogelmann]

thanks! the formula that you put in the file works really well.

[daddylonglegs]

Just to clarify....

separators in formulas can differ depending on regional settings so my commas probably need to be semi-colons for Dutch(?) regional settings, i.e.

=TRIM(MID(SUBSTITUTE(A1;" ";REPT(" ";99));99;99))

When I open Fotis's file I see commas but I presume you see semi-colons - they will change depending on regional settings...

[Fotis1991]

"Thanks" belong to ddl. Not to me!

[Fotis1991]

Yes ddl. I see semi colons. Not comma!