find and replace code?

  • Thread starter Thread starter newbie
  • Start date Start date
N

newbie

Is there a way to do a find and replace in exce 2007?

If cell a1 has '2008-wk47
Is there a way to have a construct that says, if a1 has 2008 in the
first 4 character places then search the rest of the string to see if
there is wk- and grab the next 2 numbers which is 47 and then write
out 47+52?
The 47 would represent the 47th week and 2008 would represent the last
52 weeks so it would be 47+52 for the number of weeks from the start
point to now?

Andrew
 
Will you always have a year followed by -wk followed by a number? Is
the number always two digits (like 07), or without leading zeroes?
What would you like to see if the year is 2009 or 2007, or does that
option not apply?

Please give a few more details.

Pete
 
Pete,
Here is the column from the excel file. It wont change from this
snapshot. This what i am looking at.


2009-wk20
2009-wk25
2009-wk25
2009-wk24
2009-wk26
2009-wk23
2009-wk26
2009-wk22
2008-wk40
2009-wk25
2009-wk1
2009-wk22
2008-wk47
2009-wk1
2008-wk40
2009-wk20
2009-wk23
2009-wk24
2009-wk1
2008-wk47
2009-wk1
2009-wk1
2008-wk40
2009-wk1
2008-wk47
2008-wk40
2008-wk47
2008-wk47
2008-wk47
2009-wk26
2009-wk21
2008-wk40
2008-wk40

Then here is an example of how i want parse the data with another
column with results

2009-wk23 23 ...
2009-wk26 26 ...
2009-wk22 22 ....
2008-wk40 40+52

Notice in excel i didnt finish adding the 2008 wk 40 column so u could
see what i am doing.
Does this help?
 
Put this in B1:

=RIGHT(A1,LEN(A1)-FIND("-wk",A1)-2)&IF(LEFT(A1,4)="2008","+52","")

and copy it down. This is what you'll get:

2009-wk20 20
2009-wk25 25
2009-wk25 25
2009-wk24 24
2009-wk26 26
2009-wk23 23
2009-wk26 26
2009-wk22 22
2008-wk40 40+52
2009-wk25 25
2009-wk1 1
2009-wk22 22
2008-wk47 47+52
2009-wk1 1
2008-wk40 40+52
2009-wk20 20
2009-wk23 23
2009-wk24 24
2009-wk1 1
2008-wk47 47+52
2009-wk1 1
2009-wk1 1
2008-wk40 40+52
2009-wk1 1
2008-wk47 47+52
2008-wk40 40+52
2008-wk47 47+52
2008-wk47 47+52
2008-wk47 47+52
2009-wk26 26
2009-wk21 21
2008-wk40 40+52
2008-wk40 40+52

I'm not sure if you want to see "40+52" or the result 92. If the
latter then change the formula to this:

=RIGHT(A1,LEN(A1)-FIND("-wk",A1)-2)+IF(LEFT(A1,4)="2008",52,0)

Hope this helps.

Pete
 
If this is what you want in the next column, this macro will do
col A col B macro
2009-wk20 20 Option Explicit
2009-wk25 25 Sub parseinfo()
2009-wk25 25 Dim lr As Long
2009-wk24 24 Dim c As Range
2009-wk26 26 Dim plus As String
2009-wk23 23
2009-wk26 26 lr = Cells(Rows.Count, 1).End(xlUp).Row
2009-wk22 22 For Each c In Range("a2:a" & lr)
2008-wk40 40+52 If Left(c, 4) = 2008 Then
2009-wk25 25 plus = "+52"
2009-wk1 1 Else
2009-wk22 22 plus = ""
2008-wk47 47+52 End If
2009-wk1 1 c.Offset(, 1) = Right(c, Len(c) _
2008-wk40 40+52 - InStr(c, "k")) & plus
2009-wk20 20 Next c
2009-wk23 23 End Sub
 
May be hard to copy paste so

Sub parseinfo()
Dim lr As Long
Dim c As Range
Dim plus As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("a2:a" & lr)
If Left(c, 4) = 2008 Then
plus = "+52"
Else
plus = ""
End If
c.Offset(, 1) = Right(c, Len(c) _
- InStr(c, "k")) & plus
Next c
End Sub
 
Wow, hey, that is really cool!
Thank you so much.

Now i have a bit more understanding on how to think!
Thanks for everyone who posted!

Andrew
 
Back
Top