Hi Everyone,


Please help me with your expertise. I have this type of data
(12345~Sample), that series is a code followed by the code name. Now I
want to get only the code after the ~ sign, my problem is the code is
not fix, some codes consist of 3 characters only so I cannot just use
the simple left(A1,#) formula.


Thanks in advance!


Jerome

Maybe

=MID(A1,FIND("~",A1)+1,LEN(A1))

Mike

"jhong" wrote:

Hi Everyone,


Please help me with your expertise. I have this type of data
(12345~Sample), that series is a code followed by the code name. Now I
want to get only the code after the ~ sign, my problem is the code is
not fix, some codes consist of 3 characters only so I cannot just use
the simple left(A1,#) formula.


Thanks in advance!


Jerome

Sorry you wanted the text before the tilde

=LEFT(A1,FIND("~",A1)-1

Mike

"jhong" wrote:

Hi Everyone,


Please help me with your expertise. I have this type of data
(12345~Sample), that series is a code followed by the code name. Now I
want to get only the code after the ~ sign, my problem is the code is
not fix, some codes consist of 3 characters only so I cannot just use
the simple left(A1,#) formula.


Thanks in advance!


Jerome

Your subject says you want the text before the sign and your message says
the text after the sign; so, here is both...

Befo =LEFT(A1,FIND("~",A1&"~")-1)

After: =MID(A1,FIND("~",A1&"~")+1,999)

Rick


"jhong" wrote in message
...
Hi Everyone,


Please help me with your expertise. I have this type of data
(12345~Sample), that series is a code followed by the code name. Now I
want to get only the code after the ~ sign, my problem is the code is
not fix, some codes consist of 3 characters only so I cannot just use
the simple left(A1,#) formula.


Thanks in advance!


Jerome

Thanks Mike for the reply. I've tried your formula and I am getting
the account name's from my data, is there a way to change this formula
so I can get the code only from my file? Many thanks in advance!!

On Mar 5, 4:26*pm, Mike H wrote:
Maybe

=MID(A1,FIND("~",A1)+1,LEN(A1))

Mike



"jhong" wrote:
Hi Everyone,

Please help me with your expertise. I have this type of data
(12345~Sample), that series is a code followed by the code name. Now I
want to get only the code after the ~ sign, my problem is the code is
not fix, some codes consist of 3 characters only so I cannot just use
the simple left(A1,#) formula.

Thanks in advance!

Jerome- Hide quoted text -

- Show quoted text -

Thanks Mike! You save my day!!!


On Mar 5, 4:30*pm, Mike H wrote:
Sorry you wanted the text before the tilde

=LEFT(A1,FIND("~",A1)-1

Mike



"jhong" wrote:
Hi Everyone,

Please help me with your expertise. I have this type of data
(12345~Sample), that series is a code followed by the code name. Now I
want to get only the code after the ~ sign, my problem is the code is
not fix, some codes consist of 3 characters only so I cannot just use
the simple left(A1,#) formula.

Thanks in advance!

Jerome- Hide quoted text -

- Show quoted text -