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.
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.
Hi,
Will there only ever be one such value to be extracted, or could there be more than one in the same string?
Regards
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: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
It's only one that kind of value (number with "mg") in one cell, under that cell is similar text with different values.
Separate text example.xlsx
OK, here is attachment to see what I wish to do, hope there is some solution..
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??
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
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?
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
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 "," ";"
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.
I tried to change like u said but i get a message that "the formula you typed contains an error.. "
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))
Oh, it's my foult, I type forward slash instead backslash.. Now it's working parfect. Thank you a lotttt!!!
Hi,
Look in the attached file. XOR LX solution is very good.
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.
Hi,
Glad you got the original formula to work.
Could you re-post your examples with your new desired extractions added?
Regards
Hello sure, here you have.
Tnx Separate text example.xlsx
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
How about:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),SEARCH("MG",SUBSTITUTE(A2," ",REPT(" ",100)))+10,150))
Quang PT
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.
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
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..
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?
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.
How about:
Please Login or Register to view this content.
Doesn't work, #N/A
Great! thank you
This can easily be achieved with a VBA code.......
You may also try this code if you are open to a VBA solution.....
For detail see the attached sheet.Please Login or Register to view this content.
Please find the attached sheet to see if it meets all your new criteria.
Yes now it's working exellent 1000x tnx
You're welcome. Thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks