Problem selecting only alpha characters from cell 1 and merge another cell

[Packard40]

G'day all,

I have an index of 80,000 names from an index. Some names appear in multiple volumes and on multiple pages within a volume. While the name is the same, they are different people. The Roman numeral is the volume and the numeric is the page number.

Example of original data: Joe Shmoe V-225, 310 VIII-22, 86, 110

I have separated the data into separate columns. Now I have:

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
Joe Shmoe V-225 310 VIII-22 86 110

At this point, I want to combine the Roman numeral in Col 2 with Col 3 and 4 and combine the Roman numeral in Col 4 with Col 5 and 6.

I have tried several things with no success, so at this point I need help from the experts.

There are way too many records for me to manually enter the Roman numeral where missing.

Thank you very much in advance for any assistance to solve this problem.

[stanleydgromjr]

Packard40,

Welcome to the Excel Forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[stanleydgromjr]

Packard40,

Are you going from this:
Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
Joe Shmoe V-225 310 VIII-22 86 110

To this:
Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
Joe Shmoe V-225 V-310 VIII-22 VIII-86 VIII-110

Does your workbook contain titles in row 1?

If so, I would still like to see the workbook, per the above.

[Packard40]

Thank you Stanley for your responses and please excuse me, I should have attached a spreadsheet sample of the data and desired results.

I have attached a spreadsheet for your review.

In response to your question, I do have column names, not sure if they are useable though.

[FDibbins]

Hi and welcome to the forum

If I understand you correctly, try this for col-3...
=LEFT(C3,SEARCH("-",C3,1)-2)&" - "&D3
and for col 4...
=LEFT(C3,SEARCH("-",C3,1)-2)&" - "&E3
You can then highlight both cells and copy them as a pair to the next "set"

[Packard40]

Hi FDibbins,

That worked perfectly~!!

Thank you very, very much for your timely and terrific assistance.

Respectfully,
Packard40...

[FDibbins]

Happy to help and glad it worked for you

[stanleydgromjr]

Packard40,

Thanks for the workbook.

I assume that the actual raw data worksheet is Sheet1. And, that the tiles are in row 1.

Macro execution time on your current dataset was 0.000 seconds.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CopyLeadingText()
' stanleydgromjr, 09/29/2013
' http://www.excelforum.com/excel-general/957982-problem-selecting-only-alpha-characters-from-cell-1-and-merge-another-cell.html
Dim a As Variant, s
Dim i As Long, c As Long, h As String
With Sheets("Sheet1")
  a = .Cells(1).CurrentRegion
  For i = 2 To UBound(a, 1)
    For c = 2 To UBound(a, 2)
      If InStr(a(i, c), " - ") > 0 Then
        s = Split(a(i, c), " - ")
        h = s(0) & " - "
      ElseIf InStr(a(i, c), " -") > 0 Then
        s = Split(a(i, c), " -")
        h = s(0) & " -"
      ElseIf a(i, c) = "" Then
        'do nothing
      ElseIf a(i, c) <> "" And IsNumeric(a(i, c)) Then
        a(i, c) = h & a(i, c)
      End If
    Next c
  Next i
  .Cells(1).CurrentRegion = a
  .Columns.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CopyLeadingText macro.