Hi Super Excelers
I am looking for special a Excel formula, to extract a string out of a cell.
This string (alphanumeric code) comes in two forms, which always starts with the letter S.
It is either 10 or 11 characters long.
Here is an example; SA9010001, SA9010001W, SX0512001, SX0512001L
The cells could contain a sentence. ie. [This code SA9010001 is unavailable.]
THANK YOU
Last edited by virsilens; 02-22-2018 at 05:42 PM.
is the S at the start of the character screen and does it always have a space at the end ?
can you give some example of real data where its correct and where its wrong
perhaps a spreadsheet sample attached
what comes to mind is using a test
left(cell, 1+="S"
AND
find(" ",cell,1) = 10
OR
find(" ",cell,1) = 10
if those conditions are all TRUE then you can use in an IF
=IFERROR(AND(LEFT(A2,1)="S",OR(FIND(" ",A2,1)=11,FIND(" ",A2,1)=12)),"none")
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi ETAF! Thank you for looking.
Screenshot and file attached
4299 2018-02-22.jpg
This formula will extract your string. However, it will not work if there is any other word that begins with uppercase S in the sentence.
Formula:
Please Login or Register to view this content.
So it works for your example, but it would not work for
This Service code SA9010001 is unavailable.
In such as case the formula would return the word "Service".
(You can get a more sophisticated solution with a macro that uses regex pattern matching.)
file attach
We posted at the same time, and I did not see your sample data/results. You have examples where a string begins with S but you show a result of "none". In spite of your example of a sentence, your data shows cells with only the number, and my formula does not work with those. All of these requirements are difficult to do with formulas. I would suggest the macro approach.
Also, an image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.
It will be much easier to understand your problem if you provide your file. This allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like. Otherwise we would have to build something from scratch, trying to guess what you want it to look like.
The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
Thanks 6StringJazzer
I have uploaded the sheet now. I added some better description in adjacent columns.
The formula pulls out any 10 or 11 digit string that starts with capital S; regardless of word position in a sentence.
B2Try this array formula and copy towards downPHP Code:=IFERROR(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(MATCH(1,MMULT((LEN(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)*LEN(A2)+1,LEN(A2))))={10,11})*(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)*LEN(A2)+1,LEN(A2)))&" "&1/17))<12),{1;1}),0)-1)*LEN(A2)+1,LEN(A2))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Holy mackerel! That's an impressive code nflsales.
Though it doesn't quite work when I enter it (as array).
Reattached file with your formula inserted.
4301 2018-02-22.jpg
sorry &" "& (those are actually 11 spaces, when posting here it show as 1 space) was displaying as &" "& in the my above post
see the attached file
Try this ...
=IFERROR(LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-9)),1)="S")/MID(A2,ROW(INDIRECT("3:"&LEN(A2)-7)),8)/
(MID(A2&" ",ROW(INDIRECT("11:"&LEN(A2)+1)),1)=" "),MID(A2,ROW(INDIRECT("1:"&LEN(A2)-9)),10)),
LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-9)),1)="S")/MID(A2,ROW(INDIRECT("3:"&LEN(A2)-7)),8)/
(MID(A2&" ",ROW(INDIRECT("12:"&LEN(A2)+1)),1)=" "),MID(A2,ROW(INDIRECT("1:"&LEN(A2)-9)),11)))
Try this
Enter in B2 and copy down
Formula:Please Login or Register to view this content.
v A B 1 VALUE RESULT 2 SC13000010 SC13000010 3 SC13000010-10 4 S389010426X S389010426X 5 SERV009087-10 6 S389010001 LINER - BLACK S389010001 7 SB96000010L OEM FG SB96000010L 8 SERV009087-10 OEM FG SERV009087 9 CATALOG S496010001X ONE STEP FIN S496010001X 10 DELUXE PERLIN SC09040420L SC09040420L 11 THE FOX RUNS
Last edited by AlKey; 02-22-2018 at 09:25 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
aOmGnON_700b.jpg
Phuocam and AlKey! both work perfectly
Good shot at it nflsales, even with the spaces, I couldn't make it happen, prob something on my side.
A2 = S1 SC13000010 AlKey's formula result S1,
Last edited by Phuocam; 02-22-2018 at 09:53 PM.
SOLVED - thanks all!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks