Text to Columns does not work

[Neville Bentley]

Hi Out there,

I have a report, which I get from BW (SAP Reporting tool) when I export it to Excel I get a very annoying characteristic happening with in Excel. Because it is text, excel puts a character in front of the text so that it does not get formatted - that is the only thing I can think of.
If I use the "Text to Columns" function under the Data tab I get no joy.

Does anyone know how to get rid of this "Invisible" character so that I can do a VLOOKUP on it.

Here is an example: 'UK > Australia AF I need it to look like UK > Australia AF without the ' character.

Cheers

Neville

 

[Richard Schollar]

Hi Neville

That ' should be acting as a prefix character and shouldn't be impacting on a VLOOKUP you run (unless they are numeric values in the cell, in which case the ' will convert to text). Are you sure the presence of this character is throwing out your Vlookups?

 

[Neville Bentley]

Richard,

Thanks for the reply.
I have manually taken out the ' character and the VLOOKUP works fine. Even the Find and replace does not work.

I have at least 4000 rows of data x by 4 columns of Text for a lookup function.

Cheers

Neville

 

[mikerickson]

Are the spaces CHAR(32) or CHAR(160)?

(Uh, no they aren't, removing the ' works...)

 

[Neville Bentley]

Mike, Thanks for the reply.

I do not know how to find that out. It is a straight data dump from a BW cube (R3 SAP).

Perhaps a bit of VBA code - as in a Function or macro I could put into my "Personal.xls" and run it when the need arises?

Cheers

Neville

 

[mikerickson]

To test one cell
=FIND(CHAR(160),A1) will be instructive.
But if removing the appostrophy fixes things, then I doubt that is the problem.

This will remove all the leading apostrophyies (and convert formulas to values)

Sub test()
Dim outArray As Variant
Dim i As Long
Dim j As Long
With ActiveSheet.UsedRange
    ReDim outArray (1 To .Rows.Count, 1 to .Columns.Count)
    For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count
            outArray(i, j) = .Cells(i, j).Text
        Next j
    Next i
    .Value = outArray
End With
End Sub

 

[RoryA]

Hi,
Try this: select your data, switch to the VBEditor (Alt+f11), then open the Immediate window (Ctrl+G), type this in:

selection.formula = selection.value

and press Enter.
HTH

 

[Neville Bentley]

Thanks everybody who helped - It is greatly appreciated.

Neville