Macro to take number from a cell

[3thr3e]

Hi,

As part of my work, each week I export a spreadsheet from our hotel system which contains info I need. However a lot of this info is meaningless and I find myself only copying and pasting the parts I need. I am new to the whole macro thing and feel I am confident in automating the copy and pasing process.

However one of the things I currently have to do which is very tedious is the following.

There is a column with hundreds of lines of data which all look about the same:

<TABLE style="WIDTH: 419pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=559 x:str><COLGROUP><COL style="WIDTH: 419pt; mso-width-source: userset; mso-width-alt: 20443" width=559><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 419pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=559><TABLE style="WIDTH: 419pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=559 x:str><COLGROUP><COL style="WIDTH: 419pt; mso-width-source: userset; mso-width-alt: 20443" width=559><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 419pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=559>Room# 9999 : CHECK# 0100064 [494]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Room# 9999 : CHECK# 0100071 [494]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Room# 9999 : CHECK# 0100055 [648]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Room# 9999 : CHECK# 0100076 [494]

I am only interested in the last three digits in the square parentheses and have to systematically go down the page changing each cell to be just the three digit number. So at the end the column above would look like this:

494
494
648
494

Is there any way that I can automate this process with a macro?

Thanks!
</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[VoG]

You can do that with a formula

Excel Workbook
	A	B
1	Room# 9999 : CHECK# 0100064 [494]	494
2	Room# 9999 : CHECK# 0100071 [494]	494
3	Room# 9999 : CHECK# 0100055 [648]	648
4	Room# 9999 : CHECK# 0100076 [494]	494
Sheet1

 

[3thr3e]

Wow! Thanks for reply so fast, works a treat.

You can do that with a formula

Excel Workbook
	A	B
1	Room# 9999 : CHECK# 0100064 [494]	",A1)-FIND("-1)+0]494
2	Room# 9999 : CHECK# 0100071 [494]	",A2)-FIND("-1)+0]494
3	Room# 9999 : CHECK# 0100055 [648]	",A3)-FIND("-1)+0]648
4	Room# 9999 : CHECK# 0100076 [494]	",A4)-FIND("-1)+0]494
Sheet1

 

[Silvrhelm]

if your data starts in A1 put in B1 =SUBSTITUTE((SUBSTITUTE((RIGHT(A1,5)),CHAR(91),"")),CHAR(93),"")
and copy it down

 

[3thr3e]

Thanks VoG,

The formula you've given me works a treat, but I am trying to automate the process of replacing the whole column with just the numbers. Would I need a macro to do this?

With your current system the macro would need to:

1) Add a column to the end of the table
2) Insert the formula you gave me into A1 and autocomplete down to the bottom of the table (wherever that would be)
3) Delete column A (shifting cells to left so it's effectively replaced the column)

I don't know how to do the above but you'd run into the obvious problem that when you deleted column A, the values in column B would no longer be there.

Any further help would be much appreciated

You can do that with a formula

Excel Workbook
	A	B
1	Room# 9999 : CHECK# 0100064 [494]	",A1)-FIND("-1)+0]494
2	Room# 9999 : CHECK# 0100071 [494]	",A2)-FIND("-1)+0]494
3	Room# 9999 : CHECK# 0100055 [648]	",A3)-FIND("-1)+0]648
4	Room# 9999 : CHECK# 0100076 [494]	",A4)-FIND("-1)+0]494
Sheet1

 

[VoG]

Try this

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Value = Val(Mid(.Value, InStr(.Value, "[") + 1, InStr(.Value, "]") - InStr(.Value, "[") - 1))
    End With
Next i
End Sub

 

[Peter_SSs]

if your data starts in A1 put in B1 =SUBSTITUTE((SUBSTITUTE((RIGHT(A1,5)),CHAR(91),"")),CHAR(93),"")
and copy it down

If the [] and number are the last 5 characters in each cell (as they appear to be) then a simpler formula approach would be ..
=LEFT(RIGHT(A1,4),3)+0
.. leaving off the +0 if a text, rather than numerical, result is OK.

I am trying to automate the process of replacing the whole column with just the numbers.

In that case you could deal with the whole column at once. Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractNum()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("A")<br>        .Replace What:="*[", Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False<br>        .Replace What:="]*", Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>