reformat text using VBA

i800499

New Member
Joined
Feb 15, 2004
Messages
6
I need some help to reformat a range of text in a spreadsheet. The text is currently in the following format:

cell A1: "ABCD"
cell B1: "A1 - A5"

I need to convert the range in cell B1 so that the range is expanded downwards in column B. The output should look like this:

A1: "ABCD" B1: "A1"
A2: "ABCD" B2: "A2"
A3: "ABCD" B3: "A3"
A4: "ABCD" B4: "A4"
A5: "ABCD" B5: "B5"

Any suggestions on how to do this using VBA?
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
Is it actually A1, A2, etc? What is the range of values that this could contain?
 

i800499

New Member
Joined
Feb 15, 2004
Messages
6
The range text was just an example. I'm trying to handle a technical parts list.

The part description always contains both alpha and number characters. The alpha characters always come before the numbers. There could either be 1 or 2 alpha characters at the beginning of the part name, but never more than that.

Here are some examples of the range possibilities:

XL22 - XL27
F17 - F89
AB57 - AB102
T45 - T52
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
So what would be the expected result from the 'real' data?

Something like this?

XL22
XL23
XL24
XL25
XL26
XL27
F17
F18
...
F89
AB57
AB58
...
AB102
T45
T46
...
T52
 

i800499

New Member
Joined
Feb 15, 2004
Messages
6
Hi Norie,

Yes - your example looks exactly like the output I'm trying to produce. Please have a look at my first post for a detailed example of the input and output.

To summarize - the input is 2 cells. The first cell contains a single value which is associated with a range appearing in the second cell.

As output, the value in the first cell will be repeated down through the column and the range will be "expanded" so that each value within the range appears in the column.

Let me know if that makes sense...

Regards,
Bob
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
Select A1:B1, and run this code. Or any range that contains the data as you indicated. Note that the result will automatically populate directly below.

Code:
Public Sub ReformatText()
  Dim rngSource As Range
  Dim str1 As String, str2 As String
  Dim s As String
  
  Dim n1 As Long, n2 As Long
  Dim i As Long
  Dim strPrefix As String
  
  Dim v As Variant
  
  On Error Resume Next
    Set rngSource = Selection
    If Err.Number <> 0 Then Exit Sub
  On Error GoTo 0
  
  Debug.Print rngSource.Address
  
  If rngSource.Cells.Count <> 2 Or rngSource.Columns.Count <> 2 Then Exit Sub
  
  s = rngSource.Cells(2).Value
  
  str1 = Trim(Left(s, InStr(1, s, "-") - 1))
  str2 = Trim(Right(s, Len(s) - InStr(1, s, "-")))
  
  Debug.Print str1, str2
  
  For i = 1 To Len(str1)
    If IsNumeric(Mid(str1, i, 1)) Then Exit For
  Next i
  
  If i > Len(str1) Then Exit Sub
  
  strPrefix = Left(str1, i - 1)
  n1 = CLng(Right(str1, Len(str1) - i + 1))
  
  For i = 1 To Len(str2)
    If IsNumeric(Mid(str2, i, 1)) Then Exit For
  Next i
  
  If i > Len(str2) Then Exit Sub
  
  n2 = CLng(Right(str2, Len(str2) - i + 1))
  s = rngSource.Cells(1).Value
  
  Debug.Print s, strPrefix, n1, n2
  
  ReDim v(1 To n2 - n1 + 1, 1 To 2)
  
  For i = n1 To n2
    v(i - n1 + 1, 1) = s
    v(i - n1 + 1, 2) = strPrefix & i
  Next i
  
  Debug.Print rngSource.Cells(1).Offset(1, 0).Resize(UBound(v, 1), UBound(v, 2)).Address
  
  rngSource.Cells(1).Offset(1, 0).Resize(UBound(v, 1), UBound(v, 2)).Value = v
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Bob

I think we need to know exactly what you want to do here - I'm a little confused.

In your first post and your third it appeared that you were working with data in one cell, not two.:eek:
 

i800499

New Member
Joined
Feb 15, 2004
Messages
6
Thanks for the response - I'm going to check it out now.

Saw that you are from Maryland - which part? I graduated from UMD many years ago and hoping against hope that the Terps have an answer for Duke tonight...
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,531
Bob

I think we need to know exactly what you want to do here - I'm a little confused.

In your first post and your third it appeared that you were working with data in one cell, not two.:eek:
I just assumed that you will work with 2 cells, selected prior to running the macro. The two cells are in the same row and adjacent to each other. First one contains the value to repeat, the second one contains the range (e.g. "A1 - A5"). The code will populate the cells directly below the selection.

It might be more complicated than that, but a good starting point notwithstanding.

Saw that you are from Maryland - which part? I graduated from UMD many years ago and hoping against hope that the Terps have an answer for Duke tonight...
I'm currently staying in Columbia. Got my undergrad from UMD several years ago.
 

i800499

New Member
Joined
Feb 15, 2004
Messages
6
This solves the most difficult portion of my problem. I can tweak this this to get to where I need to be.

Thanks, again.
 

Forum statistics

Threads
1,082,276
Messages
5,364,196
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top