Adding apostrophe in front of numbers only

[22strider]

Hi

Could any one please help me adding apostrophe (') in front of the data with number format only?

I have a list of part numbers that I am trying to import to Access. But after importing the records with format "number" appear as "#Num!". But if I add apostrophe in front of the records then the record appear fine in Access. But the apostrophe should only be in front of the numbers only not in front of text.

I am new to writing codes in VBA so can't separate numbers from text for adding apostrophe. Any help is appreciated.

Thanks
Rajesh

 

[rdw72777]

Does the ISNUMBER function work in this example? Not sure if you;re familiar, but Excel has an ISNUMBER function where if its a numeric it will return True but if not it will return False. It might be easier than doing code, but only if those values are truly numeric still.

 

[Norie]

Rajesh

How are you doing this import/export?

You mention code, can you post that code?

 

[22strider]

Helllo Ryan

Thanks for the idea; I used it in writing following code in VBA. It is "ISNumeric" for VBA

"Sub AddApostrophe()

Dim LValue As Boolean
Do While Not IsEmpty(ActiveCell)

LValue = IsNumeric(ActiveCell.Value)
If LValue = "True" Then
ActiveCell.Value = "'" & ActiveCell.Value
Else
ActiveCell.Value = ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub"

Thanks again
Rajesh

 

[Moid]

Is there a reason the apostrophe needs to be there? If it is just to change the values into text then you could just format the cell using the "@" format. Not sure if that helps?

 

[22strider]

It is just because MS Access was being picky and won't see the imported part numbers from excel any other way.

 

[22strider]

my code worked for me though.