Search a text cell for a valid value.

  • Thread starter Thread starter Tony Steane
  • Start date Start date
T

Tony Steane

Greeting

Without using VBA.

Is it possible to search a text cell and return the start position
within that cell if valid text is found.

example
If cell A1 had the text "Ringwood North Vic 3134",
or "Sydney NSW 2001"

The point is that I will not know the cells content, but I want to
test if the text contains either Act,NSW,QLD,SA,TAS,Vic,WA and return
the starting position of that text.

I have tried using Nested IF's with search,find,match etc but once an
invalid test is encountered, #value is returned.

Any ideas

Cheers

Tony
 
Rick,

Maybe nested Ifs arent the way to go but if you did:

=IF(ISERROR(FIND("ACT",A2,1))=FALSE,FIND("ACT",A2,1),IF(ISERROR(FIND("NS
W",A2,1))=FALSE,FIND("NSW",A2,1),IF(ISERROR(FIND("QLD",A2,1))=FALSE,FIND
("QLD",A2,1),IF(ISERROR(FIND("VIC",A2,1))=FALSE,FIND("VIC",A2,1),IF(ISER
ROR(FIND("SA",A2,1))=FALSE,FIND("SA",A2,1),IF(ISERROR(FIND("WA",A2,1))=F
ALSE,FIND("WA",A2,1),FIND("NT",A1,1)))))))

This will return the position of the State, and an error is any state
code isnt found.

Regards
Weasel

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
The best I've managed has been :

=IF(NOT(ISERR(FIND("ACT",A9))),FIND("ACT",A9),IF(NOT(ISERR(FIND("NSW",A9))),FIND("NSW",A9),IF(NOT(ISERR(FIND("QLD",A9))),FIND("QLD",A9),IF(NOT(ISERR(FIND("SA",A9))),FIND("SA",A9),IF(NOT(ISERR(FIND("TAS",A9))),FIND("TAS",A9),0)))))+IF(NOT(ISERR(FIND("VIC",A9))),FIND("VIC",A9),IF(NOT(ISERR(FIND("WA",A9))),FIND("WA",A9),0))

I ended up splitting the nested IF's as excel refused to accept it
possibly because of the length. Note it is case specific.

Dunca
 
Thanks Guys.

They were the solutions I were looking for.

Like they say, "It's easy when you know how.."

Cheers

Tony
 
Back
Top