I am running code to check through each column in a worksheet and make
substitutions for various characters, e.g.

For i = 1 To numcols
For j = 1 To numrows - 1
fvalue = ActiveCell.Offset(j, 0)
Call cleanse_data
ActiveCell.Offset(j, 0).Value = StrConv(fvalue,
vbUpperCase)
Next

Sub cleanse_data()
'data cleansing routine

fvalue = Application.WorksheetFunction.Substitute
(fvalue, "&", " AND ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "#", " NO. ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "/", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "/", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "_", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, " ", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, " ", " ")
End sub

my problem is that when the value is written back into the cell
"numbers" which were previously formatted as text revert to a number
format, e.g. "000888" reverts to "888".

How can I run this code, or similar code and retain the original
formatting?

I can't use a leading apostraphe as these values are uploaded to
database system.

Would appreciate any input ...
Julie

Not sure that I really understand the question properly. Do you mean that you
have strings that are a mixture of numerics and other characters and after
removing other characters you are left with a string of purely numerics with
leading zeros and you want to retain the leading zeros?

If the above is correct then try formatting the cells to text (either prior
to running the code or within the code.)

The following example might help to point you in the right direction.
The code takes a string with a mixture of numeric and alpha, substitutes
nothing for the alpha characters leaving numerics with leading zeros.

It then tests for all numerics in the string.
If all numerics then the activecell is formatted to text and assigns the
string to the cell. (leading zeros will be retained)
Else it simply assigns the text to the cell without formatting.

Note that some databases when importing data do not like a mixture of text
and numerics in the same column so you might be better to simply format the
entire column to text.



--
Regards,

OssieMac


"needhelp" wrote:

I am running code to check through each column in a worksheet and make
substitutions for various characters, e.g.

For i = 1 To numcols
For j = 1 To numrows - 1
fvalue = ActiveCell.Offset(j, 0)
Call cleanse_data
ActiveCell.Offset(j, 0).Value = StrConv(fvalue,
vbUpperCase)
Next

Sub cleanse_data()
'data cleansing routine

fvalue = Application.WorksheetFunction.Substitute
(fvalue, "&", " AND ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "#", " NO. ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "/", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "/", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, "_", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, " ", " ")
fvalue = Application.WorksheetFunction.Substitute
(fvalue, " ", " ")
End sub

my problem is that when the value is written back into the cell
"numbers" which were previously formatted as text revert to a number
format, e.g. "000888" reverts to "888".

How can I run this code, or similar code and retain the original
formatting?

I can't use a leading apostraphe as these values are uploaded to
database system.

Would appreciate any input ...
Julie

Hello Julie,

Something failed as I was trying to copy the code before so here it is again.

Not sure that I really understand the question properly. Do you mean that
you have strings that are a mixture of numerics and other characters and
after removing other characters you are left with a string of purely numerics
with leading zeros and you want to retain the leading zeros?

If the above is correct then try formatting the cells to text (either prior
to running the code or within the code.)

The following example might help to point you in the right direction.
The code takes a string with a mixture of numeric and alpha, substitutes
nothing for the alpha characters leaving numerics with leading zeros.

It then tests for all numerics in the string.
If all numerics then the activecell is formatted to text and assigns the
string to the cell. (leading zeros will be retained)
Else it simply assigns the text to the cell without formatting.

Note that some databases when importing data do not like a mixture of text
and numerics in the same column so you might be better to simply format the
entire column to text.

Dim fValue As String

fValue = "000B898A"

fValue = Application.WorksheetFunction.Substitute(fValue, "A", "")
fValue = Application.WorksheetFunction.Substitute(fValue, "B", "")

If IsNumeric(fValue) Then
'Format cell as text
ActiveCell.NumberFormat = "@"

ActiveCell = fValue
Else
ActiveCell = fValue
End If

--
Regards,

OssieMac

thank you OssieMac

I had actually tried using

ActiveCell.Offset(j, 0).NumberFormat = "@"

in my code before posting my question and it didn't work.

It does now, so i'm off to have a coffee!

Cheers
Julie

Hi again Julie,

Did you have the number format in the correct place in the code in your
initial test?

The number format must be performed before assigning the value or it will
not work because the leading zeros will have already been removed and while
it will become text, it cannot replace the leading zeros.

Anyway pleased for you that you have it working now so good luck.

--
Regards,

OssieMac


"needhelp" wrote:

thank you OssieMac

I had actually tried using

ActiveCell.Offset(j, 0).NumberFormat = "@"

in my code before posting my question and it didn't work.

It does now, so i'm off to have a coffee!

Cheers
Julie