How to convert text block to one column

Dear Experts,
I know how to convert more or less quickly a column into a semicolon separated text block: Copy column to word, convert to text, find and replace backspace with comma = done.
But I do not know how to convert such a semicolon separated text block back into a column in Excel?
There might be also a better way for my solution.
thank you
Nils

Asset short name:
F1;B51;B51_1N;B51_4,5N;B51_7N;BO25;BS1;BS2;CG10;CG1012;CG1014;CG1015;CG1016;CG1018;CG1019;CG102;CG103;CG104;CG108;CG108N;CG12;CG1210;CG125;CG126;CG127;CG128;CG129;CG2b;HL1;HL2;HS1;HS2;HS3;HS4;HS5;K26;KL;KL17/1;KL17/2;KL18/1;KL18/2;KL19;KL20/1;KL20/2;KL21;KL22;KL23/1;KL23/2;KL24;KL25;KL26;KL27;KL28;KL29;KL30;KL31;KL32;KL33/1;KL33/2;KL34/1;KL34/2;KL35/1;KL35/2;KL36/1;KL36/2;KL37/1;KL37/2;KL37/
Petersburg1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:

Select your column Then Go to -->Data-->Tex to Column-->Delimeted-->Next-->Then in there select the option of Other and Give -->; and hit Finish and you will be good to go...
scifo_dkCommented:
Yes, like this:

1. Copy the text into a new word file.
2. Press Ctrl+B
3. Choose replace
4. In the first field, you type ; as the character you want to exchange with a linebreak.
5. In the second field, you type ^l
6. The you click the button replace all, and you now have a list to copy to excel.
ragnarok89Commented:
You could import this from a CSV file, and specify the delimiter is a ;

If all this text already exists in a cell A!, you could run a macro to parse it into columns by clicking Data > Text to columns, and specify a ; delimiter. Lastly Transpose it into 1 column.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Petersburg1Author Commented:
Dear saurabh726:scifo_dk:and ragnarok89:

thanks for your quick answer.
saurabh726: I know that, but the issue here is that I get the result in a row not below in a column

scifo_dk: This solution I like a bit more as the text comes from word and I'm already in word, it is the reverse solution of what I currently do when I take a column listing from Excel

ragnarok89:
I don't know VBA but I can use it....so may question is still only half answered and I would appreciate further help:

The perfect solution would be:
I take text from word, copy it to a cell in Excel and with the help of a formula or macro I get the result in a column.
With the help of a second macro or better formula I get it back: A column to a text block separated by a semicolon.
thank you
Nils








scifo_dkCommented:
I am a bit unsure on what you want. But if its always the same amount of Assets, you can use this code. It takes the a text placed in cell A1, splits it up by semicolon, and then transpones it. Put the text from your description into cell A1, and run this macro to see an example. Is it something like that you're looking for?
Sub Makro4()

    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
        (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
        Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
        33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
        Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
        46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
        Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
        59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
        Array(66, 1), Array(67, 1), Array(68, 1)), TrailingMinusNumbers:=True

        
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub

Open in new window

Petersburg1Author Commented:
Hi Sub Makro4()
thank you for your suggestion, I will check it today.

What I want:
1. Mark one column in Excel with x amount of entries. Could be 1,2,3 or 50 or more: A1:A50 for example or only A1:A10
2. Press a button and get as result a semicolon separated text block in let's say B1 cell.

3. Paste such a semicolon separated text block to B1, press a button an get as result in A1, A2 etc. a column with the values from that block.

That would be perfect solution.
thanks
Petersburg1Author Commented:
Dear scifo_dk:
Could you look once again into it? I have improved my question :-) and would still appreciate help.
thank you
Nils
scifo_dkCommented:
Sure, I was waiting for you to test the above macro and get back to me :)

Paste the test string from your question into cell A1 in a sheet, select that cell and run this code:
Sub Split()

Application.ScreenUpdating = False

Do
Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Loop Until IsEmpty(ActiveCell.Columns("A:A"))

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    
Application.ScreenUpdating = True
    
End Sub

Open in new window


Now, paste the code beneath into a new module:
Function Assemble(ByVal cellRange As Range, Optional ByVal delimiter As String)
 Dim c As Range
 Dim newText As String
 Dim Count As Integer
 Count = 0
 newText = ""
 For Each c In cellRange
  Count = Count + 1
  newText = newText & c.Value
  If Count < cellRange.Count Then
   newText = newText & delimiter
  End If
 Next
 Assemble = newText
End Function

Open in new window


This you can use the function to concatenate the range, by typing this into a random cell:
=Assemble(A1:A68;"; ")

Open in new window


Syntax is Assemble(RANGE;"Delimiter")

Try it, and get back to me if you need any changes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.