Converting Text to Number (1 Viewer)

Javalon

New member
Local time
Today, 07:50
Joined
Nov 10, 2021
Messages
16
Hi All,

I have an Excel workbook that I want to push through into Access, but the owner of the file has NOT been diligent with their data cleanliness, whatsoever. So I am now tasked with cleaning up the data before I can import it into Access.

One field I'm struggling with is a "Price per KG" field, in which, the owner has written a mixture of actual prices (in currency format), just numbers, some text (eg "this was a sample"), but the vast majority are a price followed by the text "per kg".

These "per kg" records make up the bulk of the issue.

My question: is there a way within Excel, Access or VBA to find and remove any text characters, leaving only numerical values?

1647861632585.png


Thanks in advance for any tips!
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:50
Joined
Sep 21, 2011
Messages
14,050
Use combination of Replace() and then Val()
Replace per kg with nothing, then Val().
Might need a Trim()?, test to find out.
 

Javalon

New member
Local time
Today, 07:50
Joined
Nov 10, 2021
Messages
16
REPLACE! - When you're stressed and working on something I find you often get tunnel vision. Find & replace is so simple! Just applied it and fixed 90% of the records in a couple seconds. Thanks for both solving my issue AND pointing out my stupidity:LOL:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:50
Joined
Feb 19, 2002
Messages
42,981
Did you capture the UOM to store separately before deleting it?
 

Javalon

New member
Local time
Today, 07:50
Joined
Nov 10, 2021
Messages
16
Of course, our UOM is always kg anyway, causing me more annoyance at the use of ‘per kg’ inside the ‘price per kg’ field for purchase orders that are always in kg... lol!

I now have another issue. I have the document ready to import into Access, however, I’ve been getting paste errors on numerical value columns. I selected and formatted the cells in question to numerical, used sorting to pull string values and got nothing. I inserted a column next to it (for simplification let’s call the column in question A and the test column B) and did =A+1 to check if I had any #VALUE errors. I got nothing. I then used the function =ISNUMERICAL(A) against all records, all returned TRUE. I then copied the column and pasted values, to eliminate any formulae that may have existed. As a last resort, I added zeros to any records which were blank, usually wouldn’t cause an issue but nothing else left to try. Still won’t paste into the access table.

Any ideas?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:50
Joined
Sep 21, 2011
Messages
14,050
Have you tried Trim()
 

Javalon

New member
Local time
Today, 07:50
Joined
Nov 10, 2021
Messages
16
I just tried it, no luck. Am I able to upload files here? I could drop the column in question into a blank book to see if any of you geniuses can figure it out.

(Of course would not include any sensitive information)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:50
Joined
Jul 9, 2003
Messages
16,245
How about this:-

Quickly Extract Numbers From Text Strings With Kutools Function In Excel

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:50
Joined
Jul 9, 2003
Messages
16,245
And This:-

How to remove text and keep numbers in Excel cells

 

Gasman

Enthusiastic Amateur
Local time
Today, 07:50
Joined
Sep 21, 2011
Messages
14,050
I just tried it, no luck. Am I able to upload files here? I could drop the column in question into a blank book to see if any of you geniuses can figure it out.

(Of course would not include any sensitive information)
Yes, you have enough posts.
Give details of the steps to take though.
 

Javalon

New member
Local time
Today, 07:50
Joined
Nov 10, 2021
Messages
16
I, uh, fixed it.
Completely exasperated, I just copied and pasted the whole lot into a fresh book, and went through every single one of my aforementioned steps again. Then, it worked. I have no idea why it now worked, and I'm somewhat beyond caring. :sleep: What was supposed to have been a quick task, if somewhat mind numbing, has taken all day.

Thanks for help tho guys, appreciate it (y)
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:50
Joined
Sep 21, 2011
Messages
14,050
I, uh, fixed it.
Completely exasperated, I just copied and pasted the whole lot into a fresh book, and went through every single one of my aforementioned steps again. Then, it worked. I have no idea why it now worked, and I'm somewhat beyond caring. :sleep: What was supposed to have been a quick task, if somewhat mind numbing, has taken all day.

Thanks for help tho guys, appreciate it (y)
Perseverance. :)
A quality lacking in a good number of people. :(
 

Cotswold

Active member
Local time
Today, 07:50
Joined
Dec 31, 2020
Messages
521
I have a series of Functions that convert anything to a number.

For example:
Function Valid_Long(PassedVar As Variant) As Long
Function Valid_Single2Dec(PassedVar As Variant) As Single
.... etc, etc

The functions will convert whatever the PassedVar is to the type of number you require. If it isn't passed anything that converts, or is passed a Null, then it will return a zero. This way you will never get any Nulls into your data. All you will ever get is a valid number that you require or a zero.
( I never use Currency because it retains 16 places or decimals, regardless of the setting, and in some instances can result in rounding errors. )

I have similar Functions for strings :
Function Valid_String(PassedVar As Variant, StrVarLen As Integer) As String

It will return a string of the length StrVarLen regardless of whatever the PassedVar is. If it cannot make a valid string then it will return Space(1) but again never a null. So any number passed will end up as a string of a specific length. As will a date or anything else.

I also have functions for dates to convert a date 22/03/2022 to 20220322, or 22ndMar2022, or 22ndMar22 or Mar22, or whatever date format I want to display for the program in use.

Using this type of Function you simply call them for each operation. It is by far the easiest way. They will also work in queries of any sort, so ideal for imports.
 

Users who are viewing this thread

Top Bottom