+ Reply to Thread
Results 1 to 35 of 35

Separate text in cell by specific value/text

  1. #1
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Question Separate text in cell by specific value/text

    Hello,
    i want to separate text "54253 name 121mg 321FCT" from cell A1 to other cell where the result must be only text which contain "mg". So there in cell A2 should be result 121mg.

    Is this posible?

    Tnx to all.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Hi,

    Will there only ever be one such value to be extracted, or could there be more than one in the same string?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Separate text in cell by specific value/text

    Something like this might work for you. It depends upon the extremes of the string from which you want to extract the mg value

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    It's only one that kind of value (number with "mg") in one cell, under that cell is similar text with different values.

  5. #5
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Separate text example.xlsx


    OK, here is attachment to see what I wish to do, hope there is some solution..

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Quote Originally Posted by Ssaamirr View Post
    It's only one that kind of value (number with "mg") in one cell, under that cell is similar text with different values.
    But your attached gives a case where there are two such values in a single cell?

    Regards

  7. #7
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Yes thats right, but it's written all together, i wonder if it's possible to focus on text beatween two space's.

    My way - Find "mg" after that text must be space, then copy data from right to left until next space? something like that??

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Must be simpler, but this should work:

    =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),255*(MMULT(LEN(SUBSTITUTE(LEFT(A2,LOOKUP(2,1/(MID(A2,ROW($1:$1000),2)="MG"),ROW($1:$1000))+2),{""," "},"")),{1;-1})-1),255))

    Regards

  9. #9
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Thanks, but it show me error #VALUE! i use excel 2007 and change all "," to ";" the text that i want to operate with is in A1 cell.. What to do?

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Did you try it on the attachment you yourself posted?

    The first entry was in A2, so naturally the formula was set to refer to that cell...

    Regards

  11. #11
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Separate text example.xlsx

    Just tried and didn't work, could you upload your file or something? there is my with your function and changes "," ";"

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Ah,

    I have a feeling that array constants in your version of Excel use a different separator. Try replacing:

    {""," "}

    with:

    {""[backslash]" "}

    Regards

    Edit: the editor won't let me type a "backslash" - hope you know what I mean.

  13. #13
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    I tried to change like u said but i get a message that "the formula you typed contains an error.. "

  14. #14
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Does the multiply (*) sing has to do something with that? Should I chanege it to some other sign or what?

    =TRIM(MID(SUBSTITUTE(A2;" ";REPT(" ";255));255*(MMULT(LEN(SUBSTITUTE(LEFT(A2;LOOKUP(2;1/(MID(A2;ROW($1:$1000);2)="MG");ROW($1:$1000))+2);{"";" "};""));{1;-1})-1);255))

  15. #15
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Oh, it's my foult, I type forward slash instead backslash.. Now it's working parfect. Thank you a lotttt!!!

  16. #16
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Separate text in cell by specific value/text

    Hi,

    Look in the attached file. XOR LX solution is very good.

  17. #17
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Indi_Ra yes it's working, could you help me and do the same function like this but now I wish to get values which have text next to number "GRC" or "TAB" the same thing like XOR LX make it for "MG" just different lookup value?


    I try to change but it's not working

    Please.

  18. #18
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Hi,

    Glad you got the original formula to work.

    Could you re-post your examples with your new desired extractions added?

    Regards

  19. #19
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Hello sure, here you have.

    Tnx Separate text example.xlsx

  20. #20
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Thanks.

    Just change the first formula I gave you very slightly:

    =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),255*(1+MMULT(LEN(SUBSTITUTE(LEFT(A2,LOOKUP(2,1/(MID(A2,ROW($1:$1000),2)="MG"),ROW($1:$1000))+2),{""," "},"")),{1;-1})-1),255))

    Regards

  21. #21
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Separate text in cell by specific value/text

    How about:
    =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),SEARCH("MG",SUBSTITUTE(A2," ",REPT(" ",100)))+10,150))
    Quang PT

  22. #22
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Separate text in cell by specific value/text

    You may also try this...........

    Assuming A2 has the string, then in B2 try this.....
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  23. #23
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Hello, after one week using formula a found some problems/errors, see in attachment.

    Is it possible to search in base cell letters "MG" and then write all numbers which are next to "MG" letters? If there is written 23grcx50mg then write just 50 mg beacouse that are only number next to "MG"

    I hope that you understand me what I want...

    Example.xlsx

  24. #24
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Separate text in cell by specific value/text

    Hi again,

    Where have the spaces gone? You said originally:

    Quote Originally Posted by Ssaamirr View Post
    Yes thats right, but it's written all together, i wonder if it's possible to focus on text beatween two space's.

    My way - Find "mg" after that text must be space, then copy data from right to left until next space? something like that??
    Is this actually not always the case, then? Are there any other characters apart from a space and "X" which might precede the desired extraction?

    Regards

  25. #25
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Yes you're right, I said that, but in my list (10000 rows) I didn't saw some exceptions that doesn't have MG separated with space from other text..

    Now only way I see it it's some VBA code which can really read text and then operate with..

  26. #26
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Separate text in cell by specific value/text

    Better if you provide sample of all the possible strings. Because I saw that previously there was more than one occurrences of MG in one string, so what is your criteria to extract MG from them, you want the first one or the second one?

  27. #27
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Example.xlsx

    Ok, there is part of list, where are inclued all possible records of text from which I want to extract MG+number value.

  28. #28
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Separate text in cell by specific value/text

    How about:
    Please Login or Register  to view this content.

  29. #29
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Doesn't work, #N/A

  30. #30
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Separate text in cell by specific value/text

    Quote Originally Posted by Ssaamirr View Post
    Doesn't work, #N/A
    See attachment.
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Great! thank you

  32. #32
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Separate text in cell by specific value/text

    This can easily be achieved with a VBA code.......
    You may also try this code if you are open to a VBA solution.....
    Please Login or Register  to view this content.
    For detail see the attached sheet.
    Attached Files Attached Files

  33. #33
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Separate text in cell by specific value/text

    Please find the attached sheet to see if it meets all your new criteria.
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    11-26-2013
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Separate text in cell by specific value/text

    Yes now it's working exellent 1000x tnx

  35. #35
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Separate text in cell by specific value/text

    You're welcome. Thanks for the feedback.

+ 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. Search cell for multiple text , return comma separated text in separate cell if found
    By dangerdoug in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2013, 01:52 PM
  2. Replies: 2
    Last Post: 08-05-2013, 07:32 PM
  3. [SOLVED] Splitting text from one cell into separate text fragments, Located in adjacent cells
    By onsid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2013, 08:32 PM
  4. [SOLVED] Formula with specific text that inserts value from BESIDE cell with specific text.
    By jtmann in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-13-2012, 09:24 AM
  5. Replies: 24
    Last Post: 01-17-2012, 12:06 PM

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