View Full Version : Change 1 character in cell
austenr
06-07-2010, 09:55 AM
If I have something is cell A1 like "DHSPF1Grade" and I want to copy this across to B2 to B40 and increment the number in the text by one each time it is pasted can you do that?
Hi,
Presuming you mean DHSPF1Grade | DHSPF2Grade | DHSPF3Grade | etc...
Is it always going to be the same size range, or could we use the Selection and run it out from the first cell?
austenr
06-07-2010, 10:34 AM
No the text will be sifferent string lengths. Was hoping to isolate the number somehow and increment no matter where it appeared. Might be too much to ask for.
Sorry, I wasn't asking about the input string len, rather if it was always going to be x columns.
In a junk copy of your wb, see if this is close.
Option Explicit
Sub AutoComp()
Dim _
rexMatches As Object, _
rSel As Range, _
LeftString As String, _
RightString As String, _
NumberBetween As Long, _
i As Long
Set rSel = Selection
With CreateObject("VBScript.RegExp")
.Global = True
.MultiLine = False
.IgnoreCase = True
.Pattern = "([A-z]+)([0-9]+)([A-z]+)"
If rSel.Rows.Count = 1 _
And rSel.Columns.Count > 1 _
And .Test(ActiveCell.Value) Then
Set rexMatches = .Execute(ActiveCell)
LeftString = rexMatches(0).SubMatches(0)
NumberBetween = CLng(rexMatches(0).SubMatches(1))
RightString = rexMatches(0).SubMatches(2)
For i = 2 To rSel.Cells.Count
rSel(i).Value = LeftString & NumberBetween + i - 1 & RightString
Next
End If
End With
End Sub
I am at baby steps with RegExp, but I think this should grab the pattern you have shown.
Hope that helps,
Mark
Oops. You might want to add this to the IF:
And rSel(1).Column = ActiveCell.Column Then
mdmackillop
06-07-2010, 12:09 PM
="DHSPF" & COLUMN() & "Grade"
or
="DHSPF" & ROW() & "Grade"
depending which way you are going.
You can easily adjust by a fixed number if you don't start in Row or Column 1
Bob Phillips
06-07-2010, 12:43 PM
=SUBSTITUTE(B2,MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},"")))),
MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},""))))+1)
austenr
06-07-2010, 04:29 PM
Not sure what that formula does Bob but I'll give it a shot.
austenr
06-07-2010, 04:38 PM
How do you use that formula if you have data in B2?
Bob Phillips
06-08-2010, 01:06 AM
You put the formula in C3, and copy across.
Aussiebear
06-08-2010, 02:08 AM
=SUBSTITUTE(B2,MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},"")))),
MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},""))))+1)
....Stop looking everyone, I've found the "missing" launch code.:devil2:
mdmackillop
06-08-2010, 02:17 AM
I have something is cell A1 like "DHSPF1Grade" and I want to copy this across to B2 to B40
This bit confused me.:old:
mdmackillop
06-08-2010, 02:21 AM
....Stop looking everyone, I've found the "missing" launch code.:devil2:
Unfortunately it belongs to Beagle 2 (http://www.heralddeparis.com/flaw-theory-over-mars-beagle-loss/15227)
Bob Phillips
06-08-2010, 02:56 AM
This bit confused me.:old:
Oops, looks like I conveniently glossed over the A1 bit.
austenr
06-09-2010, 01:41 PM
How many years have you been writing these formulas Bob? Or is it a gift? Im sure it would take most of us (well the majority of us) a few days to come up with something that complex. Some of us never like me. :bow:
Bob Phillips
06-09-2010, 03:27 PM
Hey Austen,
I like formulas, so I play about with them as much as I can. Obviosuly, the more you use them, the better you get, ptractice makes perfect. I also like to present at conferences, so I have to make sure those skills are honed even sharper.
I had a need some time ago to extract the numeric portion of a text string in a cell; it looked a useful tool, so I saved it in my library. I do this with lots of my formulae solution, saves re-inventing them each time :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.