Move partial data from one Column two another Column in Excel

Question

On program start, Column A:A contains:

123456789123456-BC123456  
321654987654321-AD741258  
369852147258961-FG369852  
AS426953-153953751153369  
DF714258-957456351456321  
VB153965-233654963147456

I would like to move the KK000000-type data to column B:B. After the move, Column A:A Would contain:

123456789123456  
321654987654321  
369852147258961  
153953751153369  
957456351456321  
233654963147456

And Column B:B would contain:

How do I accomplish this?

You cannot change the reference cell, column A with a formula. It would cause a circular reference. If you want a formula then it would be Column B and Column C as the output. Otherwise you need to use vba. SO is not a code for me service. If you have tried something and it does not work, please put it in you original post and let us know what it is doing wrong. SO will help resolve specific problems. — Scott Craner, Mar 07 '16 at 01:38
Understood formula in column B and output in C or even D,would not be an issue. I just need Column A split into the specific data types. I have over 100,000 rows with the mixed data in it. If it wasn't mixed I would try to use split text. — JRM, Mar 07 '16 at 01:56

score 1 · Answer 1 · answered Mar 07 '16 at 02:00

If your data is exactly looks like in your post, you may use this sub.

Sub Extracts()
Dim tmpVal, leftPart, rightPart As String
    For Each cel In Range("A1:A6")
        tmpVal = Application.WorksheetFunction.Substitute(cel, "-", "")
        leftPart = Left(tmpVal, 15)
        rightPart = Right(tmpVal, 8)
        cel.NumberFormat = "@"
        cel.Value2 = leftPart
        cel.Offset(0, 1).NumberFormat = "@"
        cel.Offset(0, 1).Value2 = rightPart
    Next
End Sub

You need to edit codes for data if they have different length.

Ron Rosenfeld · Answer 2 · 2016-03-07T05:12:45.070

For a formula solution:

B1:  =IF(ISNUMBER(--LEFT(A1,FIND("-",A1)-1)),LEFT(A1,FIND("-",A1)-1),MID(A1,FIND("-",A1)+1,99))
C1:  =IF(ISNUMBER(--LEFT(A1,FIND("-",A1)-1)),MID(A1,FIND("-",A1)+1,99),LEFT(A1,FIND("-",A1)-1))

For a VBA solution, which might be faster with large amounts of data (read the comments in the code for important information:

Option Explicit
Sub SplitAndOrder()
'Declare variables
    Dim wsSrc As Worksheet, wsRes  As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes() As Variant
    Dim I As Long
    Dim V As Variant

'Set worksheets and ranges for data Source and Results
'To overwrite original, set wsRes and rRes appropriately
Set wsSrc = Worksheets("Sheet1")
Set wsRes = Worksheets("Sheet1")
Set rRes = Cells(1, 3)

'Get source data into variant array for speed of processing
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

'Dimension results array
ReDim vRes(1 To UBound(vSrc, 1), 1 To 2)

'Process the array
For I = 1 To UBound(vSrc, 1)
    V = Split(vSrc(I, 1), "-")
    If IsNumeric(V(0)) Then
        vRes(I, 1) = (V(0))
        vRes(I, 2) = V(1)
    Else
        vRes(I, 1) = V(1)
        vRes(I, 2) = V(0)
    End If
Next I

'Write results
Set rRes = rRes.Resize(UBound(vRes, 1), UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .NumberFormat = "@"
    .Value = vRes
    .EntireColumn.AutoFit
End With
End Sub

score 0 · Answer 3 · answered Mar 07 '16 at 05:07

0

B1=CHOOSE(FIND("-",A1),,,,,,,,,RIGHT(A1,15),,,,,,,LEFT(A1,15))
C1=CHOOSE(FIND("-",A1),,,,,,,,,LEFT(A1,8),,,,,,,RIGHT(A1,8))

if the data in column A appears differently than the question, B1 and C1 become 0.

answered Mar 07 '16 at 05:07

Rosetta

2,665
1
13
29

score 0 · Answer 4 · edited May 23 '17 at 12:24

I see good solution with help of Regular Expressions (RegEx). I found two good resources, where you can find some useful information about RegEx:

Related question on Stackoverflow
Microsoft article about Regular Expression Language

To use them, first you need to install reference on Microsoft VBScript Regular Expressions 5.5. Go to VBE (Alt + F11) > Tools > References... and choose it from list.

Then add new module and paste this code:

Function RegexExtract(text As String, Pattern As String) As String

    RegexExtract = "Not Found"

    Dim regEx As New RegExp
    Dim t As String

    regEx.Pattern = Pattern

    If regEx.test(text) Then

        t = regEx.Replace(text, "")
        RegexExtract = Replace(text, t, "")
    End If

End Function

That's it! Now you can use custom formula RegexExtract right from your Excel sheet. Just paste this formula in cell:

=RegexExtract("123456789123456-BC123456", "\d{15}")

This means Extract any 15 digits from string. Of course, you also could paste cell references into formula: =RegexExtract(F1;$I$1).

Conclusion

So this method could be reused for many different purposes. No need to make special task macros, or invent huge formulas. Regular Expressions can handle many different cases.

Move partial data from one Column two another Column in Excel

4 Answers4