Adding apostrophe in front of numbers only

22strider

Active Member
Joined
Jun 11, 2007
Messages
302
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

rdw72777

Well-known Member
Joined
Apr 5, 2005
Messages
723
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Rajesh

How are you doing this import/export?

You mention code, can you post that code?
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
302
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

New Member
Joined
Jan 12, 2010
Messages
21

ADVERTISEMENT

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

Active Member
Joined
Jun 11, 2007
Messages
302
It is just because MS Access was being picky and won't see the imported part numbers from excel any other way.
 

Forum statistics

Threads
1,144,571
Messages
5,725,059
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top