If some of the entries have a “-” separating the numbers and some don’t then you could compensate for this as follows:
=IF(ISNUMBER(FIND("-",a1)),LEFT(RIGHT(a1,10),5),LEFT(RIGHT(a1,9),5))
The Code() function returns character for the 1 to 3 digit code. E.g. in Excel the equations =Code(65) returns “A”. (I think; I’m on a Mac and working from memory)
A blank space character in HTML is Code(160)
A blank space character in Office is Code(32)
=Substitute(‘Cell Reference’, Code(160), Code(32))
Replaces any blank spaces copied and pasted from HTML into Office blank spaces.
This is important because the Clean() and Trim() functions will not work with Code(160) blank spaces.
=Trim(Substitute(‘Cell Reference’, Code(160), Code(32))
Removes any extra blank spaces at the beginning, the end, or between words in a cell
=Clean(Trim(Substitute(‘Cell Reference’, Code(160), Code(32)))
Removes any non-printing characters; I think. (I’ve been using the equation for so long …)
I always nest Trim() inside of Clean(), but I don’t remember the logic.
thanks for the explanation. I’ve been using the replace function and replacing blanks with nothing but that requires highlighting what I want fixed. Nice to have a formula for that.
Your formula is a little off. The function you want is CHAR() to return the character for a code. CODE() returns the ASCII code for a string. For example, CODE(“A”) returns 65.
You also want to nest the CLEAN inside the TRIM. If you have [space]-[non printing char]-[space], putting TRIM inside will remove the first space but leave the second. If CLEAN is inside, the non-printing character is removed first, then both spaces are removed by TRIM.
And one other slight correction: TRIM removes all spaces from the beginning and end of the cell, but it removes spaces from between words only if there are multiple spaces. It will still leave a single space between words.
Yes, thank you. I was working from memory. I hope **Magiver **comes back for your corrections.
What you say about the order of CLEAN() and TRIM() makes sense, although I’ve always done it the other way.
[For those who are interested, Code() & Char() are opposite functions; CODE(“A”) = 65, CHAR(65) = A]
I’m watching the thread. When I have time I’ll look up working examples of what’s discussed. I keep a sheet of useful formulas and it helps to have working examples.