delete non numberic characters

[Guest]

I am trying to come up with a "Do until Loop" macro to find and remove
non-numeric characters from all cells in a column, leaving only the numbers.
For example, if cell b6 contains
"$6,800 - ", I would like the macro to delete the "-" so that the cell will
have a value of -6,800.

I need the macro to Loop until the last row. I've tried to come up with a
macro but have not been successful. So far i have come up with this but it
stops me at the "Find" in my formula.

Sub CalculateOA()
Dim i As Integer

i = 5
Do Until [last row, what is the formula?]

Cells(i, 11).Value = Left(Cells(i, 11), (Find(("-"), Cells(i, 11)) - 1))
* -1

i = i + 1
Loop


Please help

 

[Earl Kiosterud]

David,

The first thing to do is to determine what's really in the cell, and what's
formatting. The - and $ character may be formatting. Do this in a spare
temporary column =ISNUMBER(cell) and we'll see if it's number or text. If
it's a number, you won't have any - or $ characters to remove -- you'll just
need to change the number formatting. If it's text, we'll continue with
your macro.

Or do Format - Cells - Number on the column, and play with the various
number formats and see if they give you what you want.

 

[Guest]

You need WorksheetFunction.Find and

 

[Guest]

Hey Earl-

i use a macro to format the whole column with the "number" formatting style
before I do this. But because the way the data is downloaded from my
company's database, all positive numbers show up in the following format :
5000-, 9000-, 444-. So although this data has the "number" formatting sytle,
it really isn't treated as a number by excel.

What am trying to do is find all cells in the column with a "-" at the end,
delete the "-", and multiply the number by -1 so that the numbers will come
out like this: -5,000, -9,000.

If i use the following formula and hard code it to an empty column next to
the column with the data, it works find

=LEFT(Q16,(FIND(("-"),Q16)-1))*-1

However, I need to insert this formula in a macro so that it will do the
same thing without me having to enter it in a separate column.

Please help, this is boggling my brain.

David,

The first thing to do is to determine what's really in the cell, and what's
formatting. The - and $ character may be formatting. Do this in a spare
temporary column =ISNUMBER(cell) and we'll see if it's number or text. If
it's a number, you won't have any - or $ characters to remove -- you'll just
need to change the number formatting. If it's text, we'll continue with
your macro.

Or do Format - Cells - Number on the column, and play with the various
number formats and see if they give you what you want.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

I am trying to come up with a "Do until Loop" macro to find and remove
non-numeric characters from all cells in a column, leaving only the
numbers.
For example, if cell b6 contains
"$6,800 - ", I would like the macro to delete the "-" so that the cell
will
have a value of -6,800.

I need the macro to Loop until the last row. I've tried to come up with a
macro but have not been successful. So far i have come up with this but it
stops me at the "Find" in my formula.

Sub CalculateOA()
Dim i As Integer

i = 5
Do Until [last row, what is the formula?]

Cells(i, 11).Value = Left(Cells(i, 11), (Find(("-"), Cells(i, 11)) -
1))
* -1

i = i + 1
Loop


Please help

 

[Gord Dibben]

Read Earl's post first then if you need a macro...................

Sub RemoveAlphas()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

David

Have you tried Data>Text to Columns>Next>Next>Adavnced and "trailing minus
signs" is checked.

Will move the minus signs to left side as you want.


Gord Dibben MS Excel MVP

Hey Earl-

i use a macro to format the whole column with the "number" formatting style
before I do this. But because the way the data is downloaded from my
company's database, all positive numbers show up in the following format :
5000-, 9000-, 444-. So although this data has the "number" formatting sytle,
it really isn't treated as a number by excel.

What am trying to do is find all cells in the column with a "-" at the end,
delete the "-", and multiply the number by -1 so that the numbers will come
out like this: -5,000, -9,000.

If i use the following formula and hard code it to an empty column next to
the column with the data, it works find

=LEFT(Q16,(FIND(("-"),Q16)-1))*-1

However, I need to insert this formula in a macro so that it will do the
same thing without me having to enter it in a separate column.

Please help, this is boggling my brain.

David,

The first thing to do is to determine what's really in the cell, and what's
formatting. The - and $ character may be formatting. Do this in a spare
temporary column =ISNUMBER(cell) and we'll see if it's number or text. If
it's a number, you won't have any - or $ characters to remove -- you'll just
need to change the number formatting. If it's text, we'll continue with
your macro.

Or do Format - Cells - Number on the column, and play with the various
number formats and see if they give you what you want.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

I am trying to come up with a "Do until Loop" macro to find and remove
non-numeric characters from all cells in a column, leaving only the
numbers.
For example, if cell b6 contains
"$6,800 - ", I would like the macro to delete the "-" so that the cell
will
have a value of -6,800.

I need the macro to Loop until the last row. I've tried to come up with a
macro but have not been successful. So far i have come up with this but it
stops me at the "Find" in my formula.

Sub CalculateOA()
Dim i As Integer

i = 5
Do Until [last row, what is the formula?]

Cells(i, 11).Value = Left(Cells(i, 11), (Find(("-"), Cells(i, 11)) -
1))
* -1

i = i + 1
Loop


Please help

 

[Guest]

Thanks for everyone's help. You are the most awesomeest (if that's a word).
I used the Data>Text to Columns>Next>Next>Adavnced and "trailing minus
signs" ithat you suggested and it worked perfectly.

Thanks.

Read Earl's post first then if you need a macro...................

Sub RemoveAlphas()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

I am trying to come up with a "Do until Loop" macro to find and remove
non-numeric characters from all cells in a column, leaving only the numbers.
For example, if cell b6 contains
"$6,800 - ", I would like the macro to delete the "-" so that the cell will
have a value of -6,800.

I need the macro to Loop until the last row. I've tried to come up with a
macro but have not been successful. So far i have come up with this but it
stops me at the "Find" in my formula.

Sub CalculateOA()
Dim i As Integer

i = 5
Do Until [last row, what is the formula?]

Cells(i, 11).Value = Left(Cells(i, 11), (Find(("-"), Cells(i, 11)) - 1))
* -1

i = i + 1
Loop


Please help