+ Reply to Thread
Results 1 to 17 of 17

Extract text from a string

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Extract text from a string

    Hello,

    We have a spreadsheet containing list of drawing nos, from which I want to extract the text part (i.e., fluid type) comes in between the drawing no to Column F. How can I accomplish this?

    Attached sample spreadsheet.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Extract text from a string

    Try this equation in F3 copied down

    =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, 20)))

    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by ChemistB View Post
    Try this equation in F3 copied down

    =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, 20)))

    Does that work for you?
    Hi,

    In some cells, there is two parts,. ie., "MP-SCW", "LP-SCW" and "HP-SCW" etc... With the above formula, it only extracts the first part ie., "MP", "LP" and "HP".
    Any other ways?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Extract text from a string

    Okay, this seems to work

    =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890)-60)))

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by ChemistB View Post
    Okay, this seems to work

    =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890)-60)))

    Works perfectly! Thanks a lot

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by ChemistB View Post
    Okay, this seems to work

    =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890)-60)))

    Hi ChemistB

    One more request, Will it be possible to put a hyphen in between "HP SCW", "LP SCW" and "MP SCW"... like "HP-SCW", "LP-SCW", "MP-SCW" ?

    Thank you in advance.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Extract text from a string

    =substitute(if(min(search({0,1,2,3,4,5,6,7,8,9},a3&1234567890))<5, trim(mid(substitute(a3,"-",rept(" ",20)),44,min(search({0,1,2,3,4,5,6,7,8,9},substitute(a3,"-",rept(" ",20))&1234567890,44))-44)), trim(mid(substitute(a3,"-",rept(" ",20)),22, search({0,1,2,3,4,5,6,7,8,9},substitute(a3,"-",rept(" ",20))&1234567890)-60)))," ", "-")

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract text from a string

    Or this

    =IF(OR(RIGHT(MID(A3,FIND("-",A3)+1,6))={"0","1","2","3","4","5","6","7","8","9","-"}),IF(ISERROR(MID(A3,4,1)+0),TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",25)),25,25)),TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",25)),25*2,25))),MID(A3,FIND("-",A3)+1,6))
    Last edited by AlKey; 11-25-2014 at 06:55 PM.
    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

  9. #9
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by AlKey View Post
    Or this

    =IF(OR(RIGHT(MID(A3,FIND("-",A3)+1,6))={"0","1","2","3","4","5","6","7","8","9","-"}),IF(ISERROR(MID(A3,4,1)+0),TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",25)),25,25)),TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",25)),25*2,25))),MID(A3,FIND("-",A3)+1,6))
    Thank you so much, AlKey

  10. #10
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by sharathnarayanan View Post
    Hi ChemistB

    One more request, Will it be possible to put a hyphen in between "HP SCW", "LP SCW" and "MP SCW"... like "HP-SCW", "LP-SCW", "MP-SCW" ?

    Thank you in advance.


    Thanks a lot,, ChemistB

  11. #11
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Hello… when I copy some more drawing nos. in Column A (SAMPLE file attached and highlighted in green), and copy the fomula down, it gives some error result.. Please let me know how to fix it..
    Attached Files Attached Files

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract text from a string

    There reason you get errors is because your data has changed from the original file and some new conditions are now present. At this point I would like to offer you a different approach to this problem which may work even better for you. So, instead of extracting codes from the data set, simply use code look up from the list a created in column H and this formula in F3.

    =LOOKUP(25^25,SEARCH($H$3:$H$17,A3),$H$3:$H$17)

    The code list can be extended
    Attached Files Attached Files

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract text from a string

    FWIW this works at my end (column I) with all the Drawing No. you have listed in your latest upload....though admittedly not as elegant and simple as the others.

    Entered in I3 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    File attached.

    BTW: I have not, yet looked at AlKey's latest upload.
    Attached Files Attached Files

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract text from a string

    Quote Originally Posted by AlKey View Post
    There reason you get errors is because your data has changed from the original file and some new conditions are now present. At this point I would like to offer you a different approach to this problem which may work even better for you. So, instead of extracting codes from the data set, simply use code look up from the list a created in column H and this formula in F3.

    =LOOKUP(25^25,SEARCH($H$3:$H$17,A3),$H$3:$H$17)

    The code list can be extended
    FWIW: AlKey, I really like your approach. I had a difficult time wrapping my mind around this problem.

    Thanks for posting your latest. Tell me, though, is the 25^25 used as an arbitrarily large-enough-number? It works with

    COUNTA($H$3:$H$17) as well.
    Last edited by FlameRetired; 11-28-2014 at 07:23 PM.

  15. #15
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by AlKey View Post
    There reason you get errors is because your data has changed from the original file and some new conditions are now present. At this point I would like to offer you a different approach to this problem which may work even better for you. So, instead of extracting codes from the data set, simply use code look up from the list a created in column H and this formula in F3.

    =LOOKUP(25^25,SEARCH($H$3:$H$17,A3),$H$3:$H$17)

    The code list can be extended
    Thank you, AlKey....
    Sorry for the late reply.

  16. #16
    Registered User
    Join Date
    04-04-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    95

    Re: Extract text from a string

    Quote Originally Posted by FlameRetired View Post
    FWIW this works at my end (column I) with all the Drawing No. you have listed in your latest upload....though admittedly not as elegant and simple as the others.

    Entered in I3 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    File attached.

    BTW: I have not, yet looked at AlKey's latest upload.
    Thanks, FlameRetired.. It works!

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract text from a string

    You're welcome. Glad it works, and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find and extract text string from within another text string
    By huy_le in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2013, 03:22 PM
  2. Find and extract text string from within another text string
    By huy_le in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2013, 09:01 PM
  3. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  4. [SOLVED] extract text string when you only know the last three letters of that string
    By alison0edwards in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-12-2012, 01:20 PM
  5. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1