LinkBack URL
About LinkBacks
I have a column that contains numbers and text, but sometimes the text is between numbers sometimes numbers are at the start and sometimes at the end. How do I copy just the numbers and paste them into another column.
Thanks
I have a column that contains numbers and text, but sometimes the text is between numbers sometimes numbers are at the start and sometimes at the end. How do I copy just the numbers and paste them into another column.
Thanks
Hi Willows,
Try this formula with your numbers in A1.
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
This formula works but it does not enter any decimal points in the number, see below, can this formula be amended to do this (B is the column with the formula)
Capture.JPG
Last edited by Willows59; 09-29-2018 at 05:28 AM.
Has anyone got any more ideas on this issue please
similar principle
Formula:
Please Login or Register to view this content.
I don't have latest XL version(s) so there may be a new "standard" function that does the above out of the box, so to speak.
edit: appended A1 reference (in MID) with &0 to ensure a number always returned irrespective of whether or not there are numerics in the string (e.g. apple --> 0).
Last edited by XLent; 10-01-2018 at 04:44 AM.
That works a treat thank you for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks