Replace ? in General Datatype (1 Viewer)

[noboffinme]

Hi

I'm importing a spreadsheet into Access & want to change some data before it is imported to suit the database.

I have a field that is of the 'General' datatype, most values are doubles eg; '1.7' When add code to replace '?' with '0' or just "", it still won't convert correctly & deletes the columns data.

I've had a look for why data sits in the left or right side of a cell depending on the format applied but can't find anything & I think this may be part of the problem.

Does anyone know why the cells value moves from the right side to the left of the cell if you change the format to text & what is the reason for this?

I find changing the datatype to 'text' from 'general' may make the code work & change '?' to a '0' as I need.

Any suggests appreciated

 

[NBVC]

The default justification for the TEXT format is Left justification and the default justification for Number is Right justification. This is based on the tradition of have numbers right aligned so that they are easier to read and when using fixed decimals, you can align those decimals..

The General format basically combines the two defaults and can distinguish between text entries and numeric entries and justify according to the standard.

you can overright the justification from the Format|Cells menu or from the toolbar.

if you are searching for leading apostrophes to remove them, try:

http://excel.tips.net/T003332_Searching_for_Leading_Apostrophes.html

 

[noboffinme]

Thanks NBVC

That's an interesting article.

I think I have a different problem here.

I tried recording a macro to see if it would work but no good. I don't get why telling the code to replace a question mark is deleting all fields values?? even the heading !!

Changing the columns format doesn't help, there are no spaces in front of the '?' or the numbers either.

Any suggests

 

[NBVC]

The ? is a wildcard character in Excel, it is used to denote any one character in a string.

So when you try to replace ? with anything, then all characters get replaced.

If you only want to replace the actual question mark, then precede it with a tilde (~ , key just above the tab key on a regular keyboard).

e.g.

Find What: ~?
Replace With: x

 

[noboffinme]

Thanks NBVC

That's perfect, Cheers