Macro

[Leon]

I receive, daily, a spreadsheet with a lot of data. In one column, all the
cells contain numbers and text and I want to change this to reflect the
numbers only (which must represent values). The text portion is always the
same in all of the cells. I have recorded a macro which places the cursor in
the first cell, edits the cell by backspacing 6 times (which removes the text
portion), moves to the next cell and does the same. However the number of
rows differ every day. How do I get the macro to execute as far as the extent
of the data? Can anyone help?

 

[Mike H]

Hi,

If you always wnat to remove the first 6 characters then this would strip
the first 6 characters from the used range in column and leave whats left as
a number
Right click the worksheet, view code and paste it in

Sub stance()
Dim myrange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & Lastrow)
For Each c In myrange
c.Value = (Mid(c.Value, 7)) + 0
Next
End Sub

Mike

 

[Jean-Yves]

slightly adapted to cut off last 6 characters
Sub stance()
Dim myrange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & Lastrow)
For Each c In myrange
c.Value = Left(c.Value, Len(c.Value - 6))
Next
End Sub

 

[Leon]

Hi Mike, thanks for this. Actually I need to remove the last 6 characters
which are always the same. Basically the cell looks like this:
15.60space%/MWh. The %is actually a euro sign. The values vary but not the
text. Are you saying I should paste your solution below into the spreadsheet?
Leon

 

[Mike H]

I think you may have meant

c.Value = Left(c.Value, Len(c.Value) - 6)

A lack of information from the OP makes it pure guesswork whether it was the
6 rightmost or 6 leftmost characters that are to be removed.

Mike

 

[Mike H]

Then use the line supplied by the other responder but be sure to correct the
syntax
to

c.Value = Left(c.Value, Len(c.Value) - 6)

and unless you add zero to it it will be formatted as text still so the
correct line is

c.Value = Left(c.Value, Len(c.Value) - 6) +0

Mike

 

[Jean-Yves]

Hi Leon,

See my first repply
You can as well use a formula in the next column & fill down
=LEFT(A1,LEN(A1)-4) where 4 is the number or character to remove
To use the code from Mark,( Are you saying I should paste your solution
below into the spreadsheet?)

You need to copy it to a standard VBA module :
Press aLT + F11 (to open VBA Editor), then via Menu "Insert"select Module
then paste.
To run it (save before your work, there is NO Undo comand aferr a macro run
!) press F5 in the VBA editor or In the Excel window, goto Menu Tools/Macro
.../Macros
You should see the name of the routine from Mark called "stance".
Select and Run.

HTH

 

[Leon]

OK, thanks, BUT where do I store this code. My macro usage runs to recording
and occassionally stepping into to make corrections. How do I go about
storing your list of instructions - (with the other amendments)

 

[Leon]

I assumed that by saying I backspaced 6 times to remove text inferred
rightmost but I'll keep that in mind.

 

[Jean-Yves]

Hi Mike,

Yes indeed,

 

[Leon]

Merci beaucoup Jean-Yves. I will try that and see how it goes.