LinkBack URL
About LinkBacks
I have some alpha-numeric digits in a column.
how to check for a particular alphabet in between the digits and return a specific value in other column.
attached herewith sample file for reference.
I have some alpha-numeric digits in a column.
how to check for a particular alphabet in between the digits and return a specific value in other column.
attached herewith sample file for reference.
Try this one
=IF(MID(B6,6,3)="000","all-in",IF(RIGHT(B6)="a","alpha","beta"))
Last edited by AlKey; 03-17-2014 at 01:00 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
In C6 Cell
=IF((LEN(B6)-LEN(SUBSTITUTE(B6,"-","")))>1,IF(VALUE(LEFT(MID(B6,FIND("-",B6)+1,255),FIND("-",MID(B6,FIND("-",B6)+1,255))-1))=0,"all-in",IF(RIGHT(TRIM(B6),1)="a","alpha",IF(RIGHT(TRIM(B6),1)="0","beta",""))),"")
Drag it down..
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi Vijay
See the formula in the attached that does what I think you want.
It looks like AlKey and I got the same answer. I don't know what Sixthsense is doing above...??
Last edited by MarvinP; 03-17-2014 at 10:18 AM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi MarvinP,
Lol
I was too complex because I thought what happens if the data is in variable in length with or without the separator "-"
Here is a little shorter method that will pick the number between the dashes. See if this formula makes sense to your sixthsense. The secret for this is in the trim function after blowing up all the dashes with spaces.
=IF(TRIM(MID(SUBSTITUTE(B6,"-",REPT(" ",LEN(B6))),LEN(B6),LEN(B6)))="000","all-in",IF(MID(B6,LEN(B6),1)="a","alpha","beta"))
AlKey's formula is shorter but would fail if there are more or less characters in the front part of the strings. This formula keys on the dashes and will work for different length starting characters.
Look at sixthsense comments below if you don't have any dashes or the cell is blank...
Last edited by MarvinP; 03-18-2014 at 12:42 AM.
Hi MarvinP,
Nice
When the input value is like xyz then it's resulting Beta
When there is no values in cell then it's resulting #VALUE! error.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks