• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to resolve complex text formula

Josiev

New Member
Hello,

Is there a text formula I can use to grab specific characters from a source that has varied text format and varied # of characters...Attached is a sample from this source....I need to grab just the numbers at the end of the source entry excluding the zeroes for entries that begins in 0's and excluding the space and dash "-" at the end of each entry. I have along list of this sample that I need to matchup from a different source...I tried doing text to column but the characters I need ended in different columns.

I appreciate any feedback.
 

Attachments

  • Text Formula.xlsx
    8.7 KB · Views: 17
B2: =SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER( --MID(A2,ROW($1:$50),1)) * ROW($1:$50),0),ROW($1:$50))+1,1) *10^ROW($1:$50)/10) Ctrl+Shift+Enter

copy down

The digit 50 in the formula must be greater than the length of the string in cell A2
 
or

=MID(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)-MIN(SEARCH({1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1) Ctrl+Shift+Enter

Copy down
 
Hello,

Hui...thank you for the solution you gave, much appreciated.

Narayan...your formula worked really well. Thank you!
 
or

=MID(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2)-MIN(SEARCH({1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1) Ctrl+Shift+Enter

Copy down
Small note, CSE is not required for this.

It'd be necessary only IFERROR construct was used like below:
=MID(A2,MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9},A2),2^15)),LEN(A2)-MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9},A2),2^15))-1)

The other interesting formula I remember is:
=NPV(-0.9,,IFERROR((MID(A2,256-COLUMN(A:IV),1)%),""))
Link to relevant discussions which discuss this one are:

http://www.mrexcel.com/forum/excel-questions/628855-parse-numerics-3.html

http://chandoo.org/forums/topic/multiple-substitute-extract-numbers-from-alphanumeric

which sometimes makes me think that the users shall use Search facility (especially newer users) more frequently!
 
Back
Top