I have a worksheet that has different values with varying lengths and I am looking to extract all numbers from each cell. I have attached my workbook, current formula and the solution I am trying to arrive at. I understand my formula is replacing all "-" with " ", but would like to keep those if possible.
Current formula:
Please Login or Register to view this content.Thank you for your help!Please Login or Register to view this content.
Try array entering this formula in C2 and fill down.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
If you have thousands of these the formula will need some modification.
Dave
That formula needs an extra step. I forgot to remove the ","s. Try this ... still array entered.Formula:Please Login or Register to view this content.
Regular formula
Formula:Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
try this array formula(CSE)
=SUBSTITUTE(SUBSTITUTE(MID(LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW($1:$255),1)),ROW($1:$255)))),MIN(IF(ISNUMBER(--MID(A2,ROW($1:$255),1)),ROW($1:$255))),LEN(A2)),",",),"$","")
copy down
Re: Extract all numbers from cells with varying lengths
or try PowerQuery add-in for Ex2013 Pro Plus (free from MS site)
Look at Result table (you can see but it doesn't work without add-in)
A non array solution
Formula:Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
=ReplaceIt(A2)
for anyone who prefers shorter formulas
UDF (code in standard module)
Please Login or Register to view this content.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks