Strip hidden characters in a cell?

[Weldon]

I copy data from a "main frame" type system and paste the data into Excel. Here is an example:

[pre]

@ 087-911-153
  200-872-404
@ 095-885-477
  205-008-592
@ 074-614-622
  094-121-771
  072-348-032

[/pre]

The column of data can be fairly large. I then have to go to each cell manually --after using "search and replace" on characters such as @ and -)-- and backspace out hidden characters. How can I strip the hidden characters and left align the numbers in the cells?

 

[Jai9]

Hi,


Try this.


Assuming all numbers are 9 characters long and you have "@" and "-" in the data


=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"@ ",""),"-",""),9))


Not an elegant solution. But seems to work.


Jai

 

[Weldon]

Jai,


I put you formula in the empty cell above my highlighted column of numbers I needed to address, and the formula produced a perfect result for the first cell (see below).


How can I get the entire column to yield the desired result?

[pre]

205212168
  205-212-168

[/pre]

It left aligned the result which is what I need to happen, great.

 

[Debraj]

Hi Weldon,


Are you able to afford a Function for this..


Go to VBA (Alt + F11) >> Insert New Module >> Paste the below Code..

[pre]

Function SpecialTrim(txt) As String
' Removes all Special Chr from the Txt argument
Dim i As Long
SpecialTrim = ""
For i = 1 To Len(txt)
If Not Mid(txt, i, 1) Like "[ @-]" Then
'Add some more cahracter at "[ *** ]" if you want some more..
SpecialTrim = SpecialTrim & Mid(txt, i, 1)
End If
Next i
End Function[/pre]
Now come back to Excel (Alt + F11)

and in B2 type Formula as

=SpecialTrim(A2)

Please let us know if it's working perfectly..


Regards,

Deb

 

[Jai9]

Hi,


Did you put the complete formula exactly as it is?


I was also facing the same issue.I was not able to get rid of the 2 spaces.So I used RIGHT.


The RIGHT function is pulling 9 digits from right which are the numbers.


Now the result I get is:


087911153

200872404

095885477

205008592

074614622

094121771

072348032


Jai

 

[Luke M]

Can't you just copy the formula down the column?

 

[Jai9]

Hi Debraj,


I tried the UDF and got this result.


087911153

 200872404

095885477

 205008592

074614622

 094121771

 072348032


Can we get rid of the leading spaces in some of the results?


Jai

 

[Weldon]

Jai, it did work perfectly!


Is there a way to insert a capital A before each of the nine digits?

 

[Weldon]

Debraj,


I haven't tried your approach to this problem, but I did copy the formula down a column to the right of data as suggested by Luke M, and this did the trick; however, as a last step I needed to add a capital A to the left of the data (numbers). Any ideas?


I cannot thank everyone for their gracious time and assistance on this matter. Please know I am deeply grateful to you and everyone in this forum.


Weldon

 

[Debraj]

Hi Weldon,


Please change the above Function's a single line (4th) from

SpecialTrim = ""

to

SpecialTrim = "A"

Regards,

Deb

 

[Luke M]

You can just concatentate the "A" onto the beginning of Jai's formula like so:

="A"&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"@ ",""),"-",""),9))

 

[Weldon]

Luke, Debraj, Jai


Again, thak you all for your good help.


I remain deeply grateful


Weldon

 

[Debraj]

Hi Weldon,


Thank you for your kind words...


Can you please change a little bit in the code (if you are using) as finally I realise, that you need only Numeric value.. Nothing else. so updated function is :

[pre]

Function SpecialTrim(txt) As String
' Removes all Special Chr from the Txt argument
Dim i As Long
SpecialTrim = "A"
For i = 1 To Len(txt)
If Mid(txt, i, 1) Like "[0-9]" Then
SpecialTrim = SpecialTrim & Mid(txt, i, 1)
End If
Next i
End Function

[/pre]
Regards,

Deb

 

[mercatog]

Hi everyone

As Roy's function, here is another way

[pre]

' Removes all Special Chr from the Txt argument
Function TrimSpecial(ByVal Txt As String) As String
Dim Lst As String
Dim i As Integer
Dim Tb

Lst = "[ @ - ] " & Chr(160)                        'Make space between characters
Tb = Split(Lst)
For i = LBound(Tb) To UBound(Tb)
Txt = Replace(Txt, Tb(i), "")
Next i
TrimSpecial = "A" & Trim(Txt)
End Function

[/pre]
Note that Chr(160) called non-breaking space is a simple space but doesn't act as a word separator

 

[mercatog]

Another way using Regular Expressions (I know that in this case a simple Excel formula can do all needed) But here function for sharing and without any loop

[pre]

'Function replace all non numeric characters
Function SpecialReplace(ByVal Txt As String) As String
Dim Rg As Object

Set Rg = CreateObject("vbscript.RegExp")
With Rg
.Pattern = "D"                                'Or "[^0-9]"
.Global = True
SpecialReplace = "A" & .Replace(Txt, "")
End With
Set Rg = Nothing
End Function

[/pre]

 

[Debraj]

Hi Weldon,


I came again..

as I just got mail from Chandoo, I am sure he is watching this post from corner..

So updated Formula is

{="A"&SUBSTITUTE(MID(F4,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},F4),"")),LEN(F4)),"-","")}

For details visit

http://chandoo.org/wp/2012/08/23/formula-forensics-no-027/

Regards,

Deb