Sep 26 2019 05:54 PM
Sep 26 2019 08:05 PM
Hello @antonio1958 , you tagged this question with "Get and Transform". Does that mean you want to do that with Power Query?
You could do a find and replace to replace a comma with the Ascii character 10.
If you want to do this with VBA, you can use code along these lines:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
Application.EnableEvents = False
Target.Replace What:=", ", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Target.WrapText = True
Application.EnableEvents = True
End If
End Sub
This code looks at columns A to C. When a cell in these columns is edited, any comma followed by a space will be replaced with a new line and the cell will be formatted to wrap text.
To use this code, right-click on the sheet tab and select "View Code". That will open the Code Editor Paste the code into the big code window area.
Sep 27 2019 03:12 AM
SolutionMany Tks Ingeborg
Yes I was looking at possibility to use Power Query functionality like split columns in row, but it is not the right way.
Really it all comes from a Power Pivot where I used this formula
=CONCATENATEX(Range;[Generic Job Profile and code];"; ")
to have values in Pivot field. And The values (Job Profile) that is Text, is only separate by a semicolon I added in the formula.
May be bis possible to modify this formula to get Job Profile followed by a semicolon in differerent row in the same cell ? I do manually with ALT+ENTER.
Tks a lot
Antonio
Sep 27 2019 03:12 AM
SolutionMany Tks Ingeborg
Yes I was looking at possibility to use Power Query functionality like split columns in row, but it is not the right way.
Really it all comes from a Power Pivot where I used this formula
=CONCATENATEX(Range;[Generic Job Profile and code];"; ")
to have values in Pivot field. And The values (Job Profile) that is Text, is only separate by a semicolon I added in the formula.
May be bis possible to modify this formula to get Job Profile followed by a semicolon in differerent row in the same cell ? I do manually with ALT+ENTER.
Tks a lot
Antonio