How to remove special characters from numbers?

[ivankesaulya]

Dear all,

I have some difficulties trying to remove special characters from numbers section.

Here is the example;

13685421965|126
33512485|223654
4698875213615|1
99876252148631|

Help 1:
I need to count how many characters before that special character.

13685421965|126 ---> =LEN(*beforespecialcharacter*)

Help 2:
I need to erase that special character and the numbers written after that special character

13685421965|126 ---> 13685421965
33512485|223654 ---> 33512485
4698875213615|1 ---> 4698875213615
99876252148631| ---> 99876252148631

I can't thank you enough if anyone can help me with these problems


Warm regards,

Ivan Kesaulya
+6281318888266

 

[Aladin Akyurek]

In B2 enter:

=LEN(LEFT(A2,FIND("|",A2)-1))

In C2 enter:

=LEFT(A2,B2)

The foregoing set up assumes that | occurs once. Anyways it operates on the first occurrence of the | sign.

 

[ivankesaulya]

In B2 enter:

=LEN(LEFT(A2,FIND("|",A2)-1))

In C2 enter:

=LEFT(A2,B2)

The foregoing set up assumes that | occurs once. Anyways it operates on the first occurrence of the | sign.

Thank you so much sir ..
It works like a charm ..

Warm Regards,

Ivan Kesaulya

 

[jolivanes]

Obviously, Aladin's suggestion (native excel) is the way to go but if it would be part of code, this might work.

Sub AAAAA()
Dim c As Range
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    c.Offset(, 1).Value = Trim(Split(c, "|")(0))
Next c
End Sub

BTW, I would remove your phone number unless you want crank calls

Just saw you answer to Aladin. It is not part of code so just save it for possible future use!!!!!

 

[Aladin Akyurek]

Thank you so much sir ..
It works like a charm ..

Warm Regards,

Ivan Kesaulya

You are welcome.