"Left" function - How to skip over a full stop in text

**Grimace** · 05-20-2010, 02:40 AM

Hi all,

this one is probably something really basic, however it is eluding me.

On the attached file, data in column A is representative of days of the week (1=Monday, 2=Tuesday etc) and is presented as a text string. Days where there is no flight are shown by a full stop or decimal point " . "

One of the "Quirks" of the presenting program is that if the string begins with a full stop followed by a 2 (showing no flight monday, but a flight tuesday) then it adds a zero "0" to the beginning of the text string (a legacy from many years ago which was designed to reduce errors of missing the dot a the very start).

What I am struggling with is in row 20 and 21. My formula is picking up a Monday somehow, despite me saying if the string starts with a zero, then to treat the second character as the first, and then qualifying if it is a full stop by the or statement "."

Hoping someone can assist .. thanks in advance.

**pike** · 05-20-2010, 02:51 AM

Hey Grimace
try something like
=IF(ISERROR(SEARCH("1",A19,1)),"","1") b column
=IF(ISERROR(SEARCH("2",A19,1)),"","2") c column
....ect for each column

**DonkeyOte** · 05-20-2010, 02:53 AM

EDIT: Similar to pike's suggestion

B4: =IF(ISNUMBER(SEARCH(COLUMNS($B4:B4),$A4)),COLUMNS($B4:B4),"")
applied to matrix B4:H31

**Grimace** · 05-20-2010, 03:08 AM

Thanks both for the prompt replies.

I was not sure where to put your formula Pike ... was going to guess at B19 and C19 etc ....
However tried DO's suggestion as it came through just as I was "Pondering" and it worked a treat.

Thanks so much again .. legends!