Remove numbers at the end of a cell

[jesseka]

I have a list of emailNumber. I need to separate the email and number into two columns (vs both in cell 1). The number varies in length of characters. I cannot split it by numbers as sometimes there is a number in the email.



I have tried this formula and it wont work, as there is no space before the number.

=LEFT(A1,LEN(A1)-LEN(MID(A1,FIND(CHAR(22),SUBSTITUTE(A1," ",CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))-1)

Example data:

[email protected]3495
[email protected]232

I would like column 2 to be [email protected] and [email protected] and column 3 to be 3495 and 232


Any help would be greatly appreciated!

[zbor]

Try this in C1:

=LOOKUP(99^99, --RIGHT(A1, row($a$1:$a$10)))

And this in B1:
=Substitute(A1, b1, "")

[Glenn Kennedy]

I was about to post that (or very similar...)

=ABS(LOOKUP(10^10,--RIGHT(A1,ROW($1:$100))))

but then... what about possible leading zeros??? Still stumped with that one.

[AlKey]

And in case there some email addresses that do not have trailing numbers
in C1

Please Login or Register  to view this content.

In B1

Please Login or Register  to view this content.

v	A	B	C
1	[email protected]	[email protected]	3495
2	[email protected]	[email protected]	232
3	[email protected]	[email protected]

[jesseka]

Try this in C1:

=LOOKUP(99^99, --RIGHT(A1, row($a$1:$a$10)))

And this in B1:
=Substitute(A1, b1, "")

Perfect, luckily I didn't have leading zeros! (re Glenn)

I did change the second one to be A1,C1 (if anyone else needs to use the formulas)


Thanks so much!

[Glenn Kennedy]

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[FlameRetired]

An afterthought in case there should ever be leading zeros.

Please Login or Register  to view this content.

	A	B	C
1	[email protected]	[email protected]	03495
2	[email protected]	[email protected]	232
3	[email protected]	[email protected]

[tamthat]

Copy data into 2 columns
Col 1: Press Ctrl + H, Line on type: .com* , line below type: .com
Replace All =>> done
Col 2: Press Ctrl + H, Line on type: *.com , line below type don't type anything
Replace All =>> done

[zbor]

I did change the second one to be A1,C1 (if anyone else needs to use the formulas)

Yes, mine mistake while typing.
There could be more problems like

Please Login or Register  to view this content.

So if you run on any undesired output on above approaches write it back so we can create workaround.

[perpectuals]

Awesome replies.

[perpectuals]

Awesome replies.