Problem cleaning data for import

Hi
I am having a problem with a data import from CSV.

The data is problematic and as such requires cleaning before it can be imported.

Below is the section in my VBA that prepares the column for importing.

Please Login or Register  to view this content.

The data used to be OK but the app version column now brings down numeric values with multiple periods and "N/A". The upshot is that when I examine the sheet in Access - the app version column is blank.

I guess I need to replace the N/A with a numerical value e.g. 0, but I am unsure how to force the remaining data to be numerical.

Any advice much appreciated.

Many thanks

Welcome to the forum

Attach a sample csv file but change the real data to false in advance to keep it confidential.
Show what the expected result should look like.

Hi aboutautism:

What is the desired resulting format of col. G?
For example, what should :
1.00.0003
2.3.7
2.7
505000
227
2.3.3
look like?
Take 2.3.3 for example/ What number is it trying to be? 2.33? 23.3? 233?

Pete

Hi Pete
We are at the hands of Apple here.
This is how they display the currently installed app version. To try to make sense of it is very difficult as there seems to be no standard with how app versions are submitted by app developers.

In that instance I guess 2.3.3
I see no benefit in considering beyond the first 5 digits (including periods).

aboutautism:

Looks to me like there's no benefit to formatting col. G to be numeric. Text will probably be fine for version numbers. A Find and Replace on N/A's to be zero's.

You can then highlight your data in G, and multiply everything by 1. (Enter 1 somewhere, copy it, highlight your column, paste special, multiply.)

HTH.

Pete

Thanks Pete
That is good advice. I will sort that out tomorrow.

Thanks again

Martin