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

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
-----------------------------------------------------------------------
"David T" wrote in message
...
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

You need WorksheetFunction.Find and

"David T" wrote:

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

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.

"Earl Kiosterud" wrote:

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
-----------------------------------------------------------------------
"David T" wrote in message
...
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

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


On Wed, 6 Sep 2006 14:01:02 -0700, David T
wrote:

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

David

Have you tried DataText to ColumnsNextNextAdavnced and "trailing minus
signs" is checked.

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


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 14:51:02 -0700, David T
wrote:

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.

"Earl Kiosterud" wrote:

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
-----------------------------------------------------------------------
"David T" wrote in message
...
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

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

Thanks.

"Gord Dibben" wrote:

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


On Wed, 6 Sep 2006 14:01:02 -0700, David T
wrote:

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