# Single cell formula to extract whole numbers?

1. ## Single cell formula to extract whole numbers?

Hello to the great Excel Forum!

I have column in wich are data or no data( isblank(true))

"The quick brown fox jumps over the lazy dog." =no numbers then no data, show "0" null.

"The quick 1234brown fox jumps over the lazy dog." =1234

"The quick 1234brown fox jumps4321 over the lazy dog." =1234+4321=5555

"The quick 1234brown fox jumps over 1 the lazy dog." =1234+1=1235

"The quick 1.234brown fox jumps over the lazy dog." =1234 <<dot EU thousand separator

"The quick 1.234brown fox jumps over 1.000 the lazy dog." =1234+1000=2234
etc.

So, there always will be space" " before or after numbers. There might be dot"." inside number or not.
There always will be let's say "The" at the beginning of cell text data.

This formula works only for first numbers, though it's search for "otp:" inside cell:
``Please Login or Register  to view this content.``

2. ## Re: Single cell formula to extract whole numbers?

Hi B.W.B,
Here is a modified formula originally written by Bosco
Enter formula in cell C245 and copy down
Formula:
`Please Login or Register  to view this content.`

 v B C 244 245 The quick 1234brown fox jumps over the lazy dog. 1234 246 The quick 1234brown fox jumps4321 over the lazy dog. 5555 247 The quick 1234brown fox jumps over 1 the lazy dog. 1235 248 The quick 1.234brown fox jumps over the lazy dog. 1234 249 The quick 1.234brown fox jumps over 1.000 the lazy dog. 2234

3. ## Re: Single cell formula to extract whole numbers?

Originally Posted by AlKey
Hi B.W.B,
Here is a modified formula originally written by Bosco
Enter formula in cell C245 and copy down
Formula:
`Please Login or Register  to view this content.`

 v B C 244 245 The quick 1234brown fox jumps over the lazy dog. 1234 246 The quick 1234brown fox jumps4321 over the lazy dog. 5555 247 The quick 1234brown fox jumps over 1 the lazy dog. 1235 248 The quick 1.234brown fox jumps over the lazy dog. 1234 249 The quick 1.234brown fox jumps over 1.000 the lazy dog. 2234
Thanks Al, thanks Bosco, but...

 text Al's & Bosco I need: OTP: 31.003L, OSTATAK U S-6! 31009 0 Nije mjer.temp na cisterni! 0 0 Gorivo iz Kopra za BP Cazin 6.619L ostatak kupcima. 6619 6619 Gorivo iz Ploča za bp Bugojno 0 0 OTP: 31.007L, bp Jajce 4.013L,ostatak 25.504L kupcima 60524 29517

What is: ROW(\$1:\$100) i have couple of thousands rows!
What is: "[<]\0"

IF there is "otp:" then search for "," and after comma use Al-Bosco formula.
IF there is R-6 or S-12 or whatever with minus then amend that number.

It's just, i need whole number separated by space" ", it can be like: fox123 or 123fox or 123 fox, and "." dot also, if it's sepp with dot i need that numbr also.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1