Extracting numbers from text

Folks,
I am trying to extract numbers from text. The attached file shows to examples using the same formula with different results and I do not see the differences why. This is an array formula.
Thank
Book1.xlsx
Frank FreeseAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
helpfinderConnect With a Mentor IT ConsultantCommented:
put this formula in the B2 cilumn (reffering to Book2.xlsx)
=1*MID(A2;MATCH(FALSE;ISERROR(1*MID(A2;ROW($1:$10);1));0);255)
then edit the cell (F2 or double click) and CTRL+SHIFT+ENTER
then drop any copy down the formula to B11

PS: you may need to change semicolons (;) in my formula to commas (,). It depends on your Regional settings.
0
 
helpfinderIT ConsultantCommented:
in the RED table your formula has in each row reference to A2 that´s why 678 is in the all table (B column)
in blue table it if working correctly, but in E2 there you have N/A because you are using array formula so you should enter that E2 cell (double click or F2 key) and not to use ENTER bude CTRL+SHIFT+ENTER
0
 
pony10usCommented:
The formula in E2 was not entered as an array formula (type in the formula and press Ctrl+Shift+Enter)

The rest of the column was entered as an array formula which is why they respond correctly.

The formula in B3-B11 all reference A2 instead of incrementing as you move down the column.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pony10usCommented:
Beat me to it helpfinder.   :)
0
 
Frank FreeseAuthor Commented:
This is interesting. When I select from the blue table E2:E11, enter in my formula and the press Ctrl+Shift+Enter I get the same results as in the red table? I looked at the red table and it appears that it is an array. I understand that my references are not changing (?) in my array, but why?
I attached a new table not getting the results I am seeking.
Book2.xlsx
0
 
Frank FreeseAuthor Commented:
ok...that worked with ","
thanks
0
 
Frank FreeseAuthor Commented:
thank you
0
 
barry houdiniCommented:
There are two ways to enter an "array formula" - either in a single cell or a range of cells - the latter normally only makes sense when the array formula itself returns a range - here your formula returns a single value, so you need to enter the formula in a single cell only, use CTRL+SHIFT+ENTER....and only then copy the formula down.

In the red version you have selected the range and used CTRL+SHIFT+ENTER, hence the same value in every cell.

Note, if you always have numbers at the end, as per your examples, this non-array formula will suffice for up to 9 digits

=LOOKUP(10^9,RIGHT(A2,{1,2,3,4,5,6,7,8,9})+0)

regards, barry
0
 
Frank FreeseAuthor Commented:
thanks for the tip barry
I always appreciate your input
0
All Courses

From novice to tech pro — start learning today.