How to clear out an ASCII 1 from data string?

[Eloise T]

I receive Excel spreadsheets weekly from technicians submitting their work. Since these sheets are often a mess I use:

.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))

to help clean up the data.

I started with TRIM which removes leading and trailing spaces, then as an added measure I added CLEAN to remove leading and trailing ASCII characters 0-31. See sanitized attachment.

Based on cell D3, the ASCII character is a 1 which is SOH (Start of Heading) and should be removed by CLEAN, but after running TRIM and CLEAN the ASCII 1 persists. Any suggests for adjusting the formula to get rid of all ASCII 1s in the future?

Thank you for any assistance.

 

[Hui]

Your formula in D9 is incorrect
It should be =CODE(LEFT(D3,1))

Hence the space is not Asciii code 1 but Asciii code 160

To fix it use: =SUBSTITUTE(D3,CHAR(160),"")

 

[Nebu]

Hi:
May be something like this

=SUBSTITUTE(D3,CHAR(160),,1)

Thanks

 

[Marc L]

Hi !

The cell D3 first character is the ASCII #160 but as for data
from the Web this character can replace any space so the
better is to convert it as a space before the TRIM function
in case of other #160 within the text :​

Sub Demo1()
    With Sheet2.[A2].CurrentRegion.Columns(4).Rows
        With .Item("2:" & .Count)
             .Value = .Parent.Evaluate("IF({1},TRIM(SUBSTITUTE(" & .Address & ",CHAR(160),"" "")))")
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[YasserKhalil]

Thanks a lot Marc .. Can you explain this part IF({1}, ...?!!

 

[Eloise T]

Your formula in D9 is incorrect
It should be =CODE(LEFT(D3,1))

Hence the space is not Asciii code 1 but Asciii code 160

To fix it use: =SUBSTITUTE(D3,CHAR(160),"")

Thank you!

 

[Eloise T]

Hi:
May be something like this

=SUBSTITUTE(D3,CHAR(160),,1)

Thanks

Thank you!

 

[Marc L]

Can you explain this part IF({1}, ...?!!

Yasser,
sometimes Evaluate method directly returns an array
but when it's not the case the IF statement (like in post #1 IF(#>"",)
where resides an array forces its results into an array …
​

 

[YasserKhalil]

Thanks a lot. I didn't get it completely. Forgive me
Can you give me more details?

Why using IF .. I have tried this line and it works fine without using the IF part

.Value = .Parent.Evaluate("TRIM(SUBSTITUTE(" & .Address & ",CHAR(160),"" ""))")

 

[Marc L]

As in the initial attachment there is only a single row
- which is a bit poor and not logical with the IF in its code ! -
so just copy down the cell D3 and retry without IF …

For only one cell no needs an array but from two an array is expected !

So when Evaluate returns a single value as an array is expected then
adding IF with an array in its formula means « Hey dude, I need an array ! »
​

 

[YasserKhalil]

I did. I copied D3 down and retried both ways using If and without IF and I have got the same results

 

[Marc L]

Yes you're right as I just test both and each works !

I remember I needed an IF with a previous formula
but when I thought about char #160 replacing any space
I updated the formula and I didn't retry without the IF.

But remember this trick 'cause one day you will need it !
​

 

[YasserKhalil]

Thanks a lot Mr. Marc
Thank you very much