Exracting Numbers from Text

[jomili]

I have a repeatting task, and my data comes in like this:
Region 1 – 2 users
Region 2 – 1 user
Region 3 – 4 users
Region 4 – 0 user
Region 5 – 1 users
Region 6 – 3 users
Region 7 – 2 users
Region 8 – 3 users
Region 9 – 0 user
Region 10 – 1 user
Region 11 – 2 users

I have specific rows for each region, so all I need is the number of users to plug into my worksheet. I'm currently using a "Text to Columns" to pull out the number of users, then pasting them into my cells, but I'd rather do it by formula, so all I have to do is paste the data into one section of my worksheet and have the values I need populate the rest. Assume in Cell B2 I want the value of "2", taken from the contents of G2, which has this string: "Region 1 – 2 users
"

[TheExcelphile]

Try the following formula entered into B2:

=MID(G2,FIND("–",G2)+2,FIND(" ",G2,FIND("–",G2)+2)-FIND("–",G2)-2)

This formula is a little complicated, but it will return the correct number of users irrespective of the number of digits. It will work correctly if there are 2 users, 22 users or 222222 users.

Don't forget to click the little star to the left of this post if you feel I helped!

[Fotis1991]

Are the users more than 9?

[zbor]

Here, try this:

Please Login or Register  to view this content.

[Marcol]

Or maybe ...

Please Login or Register  to view this content.

This can be shortened to, for up to 99 users

Please Login or Register  to view this content.

[ChemistB]

or
=--TRIM(MID(A1,SEARCH("–",A1)+1,SEARCH("user",A1)-SEARCH("–",A1)-1))
I'm sorta partial to zbor's technique though.

[jomili]

TheExcelFile,
Your formula worked, but the result was a text item, and I need numbers to create a total.

Fotis1991,
The number of users in any region should reasonably go no higher than 3 for the foreseeable future.

Zbor,
Your formula worked right off the bat (well, after I adjusted for the columns I need). You wouldn't care to explain how it works, would you?

Sorry Chemist and Marcol, I missed your posts at first.

Marcol, your's also worked right off the bat. I'd love an explanation.

Chemist, I didn't try your's since you said you preferred Zbor's.

[Fotis1991]

My question has no sense anymore. There are many excellent solutions,now.

[Marcol]

If you expect to have less than 10 users
Then

Please Login or Register  to view this content.

LEFT(A1,SEARCH("User",A1)-1) shortens your string to e.g. "Region 1 – 9 "

RIGHT(LEFT(A1,SEARCH("User",A1)-1),{1,2}) returns an array {" ", "9 "}
1* or -- coerces this to a number
To increase the array use {1,2,3}, etc. This returns {" ","9 "," 9 "} in the above example.

Finally LOOKUP(10,{#VALUE!,9}) can't find 10 so it returns the largest value found.
#VALUE! error is caused by coercing " " to a number.

10 is just a number larger than the biggest number you might find, increase this as required.
99^99 is a very large number you are extremely unlikely to ever find.

You can get away with this for less than 10 users

Please Login or Register  to view this content.

[jomili]

Marcol,
Thanks for the great explanation. So on the last formula y (for less than 10), you simplified it by using 2 with RIGHT, returning the number and the last space. But we could also use 1 with RIGHT, if

Please Login or Register  to view this content.

is changed to

Please Login or Register  to view this content.

So, we could get rid of the *1 as well, and make the formula

Please Login or Register  to view this content.

Thanks so much. Now I understand how it works, and I'll be sure to use it often.

Thanks to everyone who contributed. This was fun!

[zbor]

You can't get rid of 1* because you need it to convert value to number.
also there is no need to have different formula for all kind of values. Just use this one with 99^99 and you will cover all cases. Formula is same speed no matter do you use 10, 100 or billion.

[jomili]

Thanks Zbor. I've added that one to my toolbook.

[Marcol]

Your probably correct zbor I only used the shortened versions to make explaining them easier.

@ jomili
You aren't "getting rid" of 1* by using the double unary --.

1+, 0+, and -- all do the same thing.

[zbor]

jomili,

I've added here all solution you've got.
As you can see, yellow rows will give you correct answer in all solutions. But in excel always try to look further... And try to find solution for cases you didn't cover, but might occure.
For example, =5*A1 is valid formula... Until someone enter character into it. Then you'll get error. Sometimes you want to see that error. Sometimes you want to avoid it.

So try to play a little bit with other cases and at the end choose soultion you like best.

I'll compare here mine and Marcol solution:
They are mostly the same.
Biggest difference is that Marcol is finding word "User" in text and I rely on "-".

Now back to beggining of this post and choose what of those two approaches suits you best.
Since Marcol already explained his solution I'll give you mine explanation too.

[zbor]

This is main part of solution:

Please Login or Register  to view this content.

What it does: Replace - with 255 spaces. I could put something else too, for example @
In that case Region 2 – 1 user with =SUBSTITUTE(A1,"–",REPT("@",255)) would return:
Region 2 @@@@@....@@@@@@ 1 user

Now take 255 right characters of that and you'll get
@@@@.....@@@@@@ 1 user

Since I don't have @ but spaces, I can easily TRIM this string and onyl thing I left is:
1 user

In general, this approach is good for extracting words AFTER LAST specific character
(in other words - Region 23232 user – Region )(!%#/#)( –Region 2 – 1 user would return same result: 1 user
while Marcol solution would return 23232. So again, neither is write or wrong but both are suitable for different things.

Next step is to extract number at beggining of this string.
I choose LOOKUP solution but I could also find first space.

In that case formula would be:

Please Login or Register  to view this content.

That again has different impact on result (check example in previous post).

[jomili]

Marcol,
You're right on the "1", we're actually not getting rid of anything. It's just that I don't always think of "*1" as for the purpose of converting text to numeric, but I always think of the double unary doing that, probably because I haven't seen it used in any other way (yet).

Zbor,
Thank you so much for the side by side comparison and the explanation. It's easy to see that Zbor+ is the way to go, knowing that I WILL get bad data at some point. Thank you. I treasure these moments when I get to learn something.

[jomili]

Zbor, I noticed one "problem"; in the Zbor+ solutions, though the results appear right, when I check the length of the results all of the single-digit results show as 2 characters, with the exception of row 10, which shows as 1. I could see that tripping me up in the future. Wrapping "Trim" around the whole formula fixes that.

[zbor]

You are right, my omission:

Please Login or Register  to view this content.

[zbor]

It's easy to see that Zbor+ is the way to go, knowing that I WILL get bad data at some point.

It also has some flaws... For example from Region 4 – 03929user (number and user without space) you will get 03929user
and not just number (I put that example intentionally in the workbook).

[jomili]

Zbor,

Your correction almost corrected it. The problem remaining is that the result is text, not a number, and since I need numbers, I changed as shown below:

Please Login or Register  to view this content.

One question i still have is: why 255? Why not 10, or 20? What's magical about 255?

[Marcol]

why 255? Why not 10, or 20? What's magical about 255?

Theoretically and historically, it's the largest length of a string that native Excel formula could handle minus 1

It's nowadays lt's just a number used by oldies

Don't confuse 10, 20 and 99^99, or that scientific E number that I can never remember, with 255

255 was only used for text maximum string lengths, that scientific E number or a near approximation is for big numbers.

Think what the limits of your problem is likely to be and use accordingly ...

[zbor]

The problem remaining is that the result is text, not a number

But then again, you will have error if text is returned then advantage dissapear and I suggest you previous version (with LOOKUP).

One question i still have is: why 255? Why not 10, or 20? What's magical about 255?

Marcol is correct.
lt's just a number used by oldies. Or if you learn from them

Idea is to expres numbers (99^99 or to be more correct:
Largest allowed positive number 9.99999999999999E+307
Largest allowed negative number -9.99999999999999E+307
Largest allowed positive number via formula 1.7976931348623158e+308
Largest allowed negative number via formula -1.7976931348623158e+308)
or text by =REPT("Z", big number) that you can never reach in common usage.

That's why Marcol told you to use 100 if you would never have number bigger than 99, but we both agree that no need for guessing what would be maximum number.. Just use one of above all the time... 99^99 or 100^100 is just easier to write every time but it's not a maximum number.
However, if you would need number bigger than 3,6973E+197 in your life I'll buy you beer for your lifetime

255 is column width in Excel (right click on column -> Column width -> enter any number bigger than 255 and warning appear).
But it become a common number for such a cases because it's unlikely that you will have text in cell longer than 255.

Since total number of characters that a cell can contain is 32,767 characters you could use much bigger number and no problem if you want to.
You should if your cells are longer than 255 char.

Few testings:
a =REPT("@", 32767) would return such a string of @'s
a =REPT("@", 32768) would return #VALUE
a =REPT("A", 255)&REPT("@", 32767) would return 255 A's and rest @'s and TRIM of that would retun (32767-255) of @'s (no matter you use REPT("@", 32767)) so playing with such a huge number you must be carefull because you might get wrong result.

Imagine example -1-2-3-4-5-6-7-8-9-0-1-2-3-4-5-6-7-8-9-test replacing - with something... Total lenght shouldn't exceed 32767 'cause you could get wrong result even your initial word was very short (20-30 characters).
That's why 255. Just a habit but having on mind all of this restrictions.

[Marcol]

A last comment on 255 characters.

Try this
In A1

Please Login or Register  to view this content.

In A2

Please Login or Register  to view this content.

In A3

Please Login or Register  to view this content.

Copy A1:A3 and Paste Special > Values to remove the formulae

In B1, Drag down to B3

Please Login or Register  to view this content.

Now with Sheet1
1/. Select the whole sheet and paste to Sheet2, all is well.

2/. Move or Copy Sheet ... Check the "Create a copy" Checkbox > Okay
you will get this message with 2003

The sheet you are copying has cells that contain more than 255 characters. When you copy the entire sheet, only the first 255 characters in each cell are copied.

To copy all of the characters, copy the cells to a new sheet instead of copying the entire sheet.

Click okay and see the result of the LEN() formulae.

See this link for the limits of 2003
Excel specifications and limits
See how often 255/256 features.

My belief is that Excel is a powerful calculator with some database ability, if you need more than 255 characters in a cell you should be using Word, it's much better for writing stories.

[jomili]

Wow, I don't know how to thank you enough. Marcol and Zbor have gone out of their way to educate me on this, and I really appreciate it. I'm so grateful I'm not even going to take Zbor up on his challenge:

Please Login or Register  to view this content.

I had thought at some time I'd calculate the number of electrons spinning around the number of protons in an alternate dimension containing multitudes of universes, so than I'd need that bigger number.

Thanks loads!

[ChemistB]

I had thought at some time I'd calculate the number of electrons spinning around the number of protons in an alternate dimension containing multitudes of universes, so than I'd need that bigger number.

Not necessarily, scientists estimate the number of atoms in the known universe at under 10^82. Assuming your multitudes of universes is a million that puts it at 10^88. The most abundent atoms are hydrogen and helium but let's say the average # of electrons per atom is 12, that puts your number needed to be about 12 * 10^88 give or take an electron.

[jomili]

Darn! No free beer today!