I am posting this could I couldn't find the answer using the search. Hope it helps.
Using FIND to separate a 'code' from a 'code dash name' cell when the dash may or may not be there.
I had a column of data where a code of 1, 2 or 3 characters was almost always followed by a dash(-), then followed by a person's name. For example '01-Olivier'. The code of 01 is 2 characters.
The 'almost always' was the problem. I had a data of 'FSA', no dash.
In other words, I had to separate the variable length code from the dash and the name, and cover situations where there was no dash. I started with the function in b1 '=find("-",a1)'. This retunded either a 2, 3 or 4, but failed on the 'FSA' data.
I did the following:
In a1, the data 01-Oliver
In b1 I used =left(a1,4)
In c1, I entered -
In d1, I used +b1&c1.
in e1, I used =find("-",d1). E1 held the position of the first dash.
In f1, I used =left(d1,(e1-1))
Worked.
Using FIND to separate a 'code' from a 'code dash name' cell when the dash may or may not be there.
I had a column of data where a code of 1, 2 or 3 characters was almost always followed by a dash(-), then followed by a person's name. For example '01-Olivier'. The code of 01 is 2 characters.
The 'almost always' was the problem. I had a data of 'FSA', no dash.
In other words, I had to separate the variable length code from the dash and the name, and cover situations where there was no dash. I started with the function in b1 '=find("-",a1)'. This retunded either a 2, 3 or 4, but failed on the 'FSA' data.
I did the following:
In a1, the data 01-Oliver
In b1 I used =left(a1,4)
In c1, I entered -
In d1, I used +b1&c1.
in e1, I used =find("-",d1). E1 held the position of the first dash.
In f1, I used =left(d1,(e1-1))
Worked.
