I want to copy a text string down a row of column in this manner. The first cell is with the text string, "W01/17" but the cell to the right should be "W02/17", "W03/17", "W04/17" and so on. The normal way of draging the cell handle to the right produce the resulting cell string as "W01/18" which is not what I wanted. Appreciate any help I can get.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Copying a text string in running order
- Thread starter singcbl
- Start date
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,465
- Office Version
- 365
- Platform
- Windows
Assuming you have W01/17 in cell A1, put this formula in cell B1 and drag it across...
=REPLACE(A1,2,2,TEXT(MID(A1,2,2)+1,"00"))
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,465
- Office Version
- 365
- Platform
- Windows
Better yet, maybe... put this formula in the first cell (assumed to be cell A1) and copy it across...
=TEXT(COLUMNS($A:A),"W00\/17")
If you start in a different column, say, Column F, then use that column's letter in place of the two A's...
=TEXT(COLUMNS($F:F),"W00\/17")
Upvote
0
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
Maybe this...
Put the formula below in the first cell and drag to the right
="W0"&COLUMNS($A1:A1)&"/17"
Hope this helps
M.
Put the formula below in the first cell and drag to the right
="W0"&COLUMNS($A1:A1)&"/17"
Hope this helps
M.
Upvote
0
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
oops...
Disregard my suggestion above - it doesn't work properly
M.
Disregard my suggestion above - it doesn't work properly
M.
Last edited:
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,465
- Office Version
- 365
- Platform
- Windows
The forward slash (the second character) is the slash from the text the OP wants in his output... the backslash (the first character) tells the TEXT function to use the next character as the text character it is... if we did not do that, the TEXT function would see the 00/17 part of the text as being a fraction format with a denominator of 17... with the backslash, the forward slash in the 00\/17 part becomes a simple forward slash character and loses it meaning of being a signal character for fraction format and the 17 that follows it is no longer considered a denominator.
Last edited:
Upvote
0
thanks for the explanation Rick
Upvote
0
Similar threads
- Question
- Solved
- Question
