Data from SAP

**thedon_1** · 03-15-2011, 05:36 AM

At work i use a lot of data that is from SAP. Unfortunately, there seems to be something wrong with the formating of the data. If i use isnumber on one of the cells containing a number, i get false.

This messes with my lookups and other formulae.

Is there a way i need to condition my data when it's coming from SAP?

Thanks

**jwright650** · 03-15-2011, 07:30 AM

You may want to post up a small sample workbook so we can see what you are up against...make sure it has some raw data straight from SAP so the formatting is the same as what you normally see and have to deal with.

**Domski** · 03-15-2011, 07:44 AM

I work with data from SAP HR. A lot of the the ID's and other fields whilst appearing to be numeric actually export to Excel as text. It's one of those things you just have to get used to I think.

Dom

**thedon_1** · 03-15-2011, 07:45 AM

Ok cool.

Here's 2 lists of data, there's a lookup formula in there demonstrating how wrong some of the results can be.

I tried using the trim function on the SAP data but it doesn't really help with.

**arthurbr** · 03-15-2011, 07:51 AM

Enter 1 in an empty cell - Copy - Select all your data - Right Click - Paste Special - Select Multiply - OK - Text is now nubers

To apply LOOKUP you will have to sort your data first

**Marcol** · 03-15-2011, 07:57 AM

I agree with Dom

This can sometimes help.
Select the Data to convert

Text to Columns > Delimited > Finish
This should convert text to numbers.
Then format the cells to suit the number of digits you need to display
e.g.
Format Cells.... Number > Custom
Type:= 00000000

Hope this helps

**jwright650** · 03-15-2011, 07:59 AM

Originally Posted by Domski

I work with data from SAP HR. A lot of the the ID's and other fields whilst appearing to be numeric actually export to Excel as text. It's one of those things you just have to get used to I think.

Dom

Yup, the numbers are actually formatted as text.

Edit: I was too slow.....

**Domski** · 03-15-2011, 08:05 AM

I have a macro like this in my Personal.xls to consistently convert lists to text value with a set number of leading zeros:

Sub Convert_Nums()

Dim myRange As Range
Dim strFormat As String
Dim intFormat As Integer

intFormat = InputBox("How many characters?")
strFormat = WorksheetFunction.Rept("0", intFormat)

Selection.NumberFormat = "@"

For Each myRange In Selection
    If myRange <> "" Then
        myRange = Format(myRange.Value * 1, strFormat)
    End If
Next myRange

End Sub

Dom

**thedon_1** · 03-15-2011, 10:40 AM

Perfect guys, works now, thanks so much for the help.

**arthurbr** · 03-15-2011, 10:54 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save