• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and return a specific character in a cell

Alan Ramsay

New Member
Hi there,

I need to be able to search the cell contents for a specific set of values and then have the matching value returned to the cell I put the search formula in, for example:

· In cell B2 I have the following string “BCO6”.
· In cell C2 I want to add a formula that will look at, (search), B2 for the following variables, “4”, “6” or “8”. It will then return the matching value, (in this case “6”), or insert a blank “” if there is no matching value.

I have been looking on the web, playing with the FIND command and SEARCH commands but they all seem to want to return the position of the variable not the variable itself. I know I could use MID, LEFT or RIGHT if the position of the variable was fixed but it is not, i.e. in my example it might be “B6OC”.


Any ideas anyone?
 
Hi, Alan Ramsay!

Try this:
=SI.ERROR(EXTRAE(A1;SUMAPRODUCTO((SI.ERROR(HALLAR({4;6;8};A1);0))*1);1);"") -----> in english: =IFERROR(MID(A1,SUMPRODUCT((IFERROR(SEARCH({4;6;8},A1),0))*1),1),"")

Regards!
 
Narayan/SirJB7,

Many thanks for the quick responses and suggestions. Not sure if I did something wrong but both your formulas are giving me "#N/A" or "FALSE" returns. Does the format of the cell containing the data matter? Right now its formatted as General - do I need to change that?

Just to be clear I simply cut and pasted your formula's into the worksheet and change the A1 reference to the column/row combination I wanted to analyze in mine.

Narayan, to answer your first question for now I am only interested in 4,6,8 but later I would like to be able to add more characters, (they will again be numbers but stored as text).

cheers,
Alan
 
Check the attached file..

#ref to @NARAYANK991

You can store the Search string in the place.. if you want to change them randomly..
 

Attachments

  • Find and return a specific character in a cell.xlsx
    8.8 KB · Views: 4
Hi, Alan Ramsay!
Give a look at the uploaded file.
Regards!
 

Attachments

  • Find and return a specific character in a cell (for Alan Ramsay at chandoo.org).xlsx
    9 KB · Views: 1
Narayan/Debraj/SirJB7,

Thank you all so much - I downloaded all three solutions and they all work perfectly - feels like my birthday!!!

Thanks again, a big help to me today.

Cheers,
Alan
 
Hi, Alan Ramsay!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Thanks SirJB7,

Now that you mention it I do have a refinement request to the formula that would be great to get :)

Is there anyway to tell is to pick up and return the first number it finds in the cell that matches those defined in the formula? For example:

A cell contains the following data:
BOC6UU428

I would want the formula to return the "6" and ignore the other numbers in this case. Of course if the cell contained:

BOC86UU42

I would want the formula to return the "8" and ignore the rest of the numbers.

Not critical to my purpose but would be nice to have if its possible?
 
Hi, Alan Ramsay!

Change the formula from this:
=SI.ERROR(EXTRAE(A1;SUMAPRODUCTO((SI.ERROR(HALLAR({4;6;8};A1);0))*1);1);"") -----> in english: =IFERROR(MID(A1,SUMPRODUCT((IFERROR(SEARCH({4;6;8},A1),0))*1),1),"")
to this:
=EXTRAE(A1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR({4;6;8};A1));HALLAR({4;6;8};A1);LARGO(A1)+1);1);1) -----> in english: =MID(A1,SMALL(IF(ISNUMBER(SEARCH({4;6;8},A1)),SEARCH({4;6;8},A1),LEN(A1)+1),1),1)

File updated & uploaded.

Regards!
 

Attachments

  • Find and return a specific character in a cell (for Alan Ramsay at chandoo.org).xlsx
    9 KB · Views: 3
One more idea:
=MID(A1,MIN(FIND({4,6,8},A1&"468")),1)
or to get it as number (will give you #VALUE if none of the matches are found)
=--MID(A1,MIN(FIND({4,6,8},A1&"468")),1)
 
Back
Top