What is the extra "thing" in the first cell?

[tazeo]

So I have a sheet that I import data from a reporting tool. Some of the headers have an extra "thing", no idea what it is. Try to do a search for Ctrl-J, for carriage return, it doesn't find anything.

I have a VBA script that identifies columns to keep for use, when the column header is like E1 the script and a simple countif search won't see that column, but I can find F1 using both.

All_Employee_Detail Better extract eddittiinngg.xlsx
	E	F
1	Incident End Date	Actual Consequence
Employee Detail All

Any thoughts on this will be great, spending far too long looking at this for what the importance is.

 

[tazeo]

Clarification - If you copy and paste the two cells from the grab into a sheet you will see what I mean.

 

[Toadstool]

Hi Tazeo,
I think XL2BB may remove it because both headings are 18 characters and I don't see anything spurious, but try =FIND(CHAR(160),E1) because I suspect it's a non-breaking space, probably ASCII 160 which you remove with SUBSTITUTE.

 

[tazeo]

Hi Tazeo,
I think XL2BB may remove it because both headings are 18 characters and I don't see anything spurious, but try =FIND(CHAR(160),E1) because I suspect it's a non-breaking space, probably ASCII 160 which you remove with SUBSTITUTE.

Thanks for that I get #VALUE! back. I will your substitute idea a bit later and let you know.

 

[NdNoviceHlp]

The thing is likely a pilcrow. The Clean function will remove it. HTH. Dave

 

[tazeo]

Ok been playing with the answers so far, and come up with the following:

All_Employee_Detail Better extract eddittiinngg.xlsx
	E	F	G	H	I
1	#VALUE!	Incident End Date		10	Incident End Date
2	Incident End Date
Employee Detail All

Cell Formulas
Range	Formula
E1	E1	=FIND(CHAR(160),E2)
F1	F1	=LEFT(E2,LEN(E2)-1)
G1	G1	=RIGHT(E2,1)
H1	H1	=CODE(G1)
I1	I1	=CLEAN(E2)

Think I will try and work out how to run clean as a Macro,

 

[tazeo]

The thing is likely a pilcrow. The Clean function will remove it. HTH. Dave

Looks like the easiest way to go. Can't work out how to use CLEAN as a VBA script to clean the top row. Trying to make it as a macro, any advice?

Really clumsy attempt:
Sub CleanTop()
Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))
End Sub

PS starting to stray away from my comfort zones

 

[NdNoviceHlp]

Seem like this would work. Adjust sheet name to suit. Untested code...

Dim LastCol As Integer, Rng As Range, R As Range
With Sheets("sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set Rng = .Range(.Cells(1, 1), .Cells(1, LastCol))
End With
For Each R In Rng
R.Value = Application.WorksheetFunction.Clean(R.Value)
Next R

Dave

 

[tazeo]

Seem like this would work. Adjust sheet name to suit. Untested code...

Dim LastCol As Integer, Rng As Range, R As Range
With Sheets("sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set Rng = .Range(.Cells(1, 1), .Cells(1, LastCol))
End With
For Each R In Rng
R.Value = Application.WorksheetFunction.Clean(R.Value)
Next R

Dave

Worked perfectly!!!

Thanks to all, really learnt a lot

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave