Possibility to back return in an Excel cell.

New Contributor
How to write a procedure or formula to have in several cells a text that After a comma or others, return back starting a new ore in the same cell. This is possibile with ALT + Enter, but I need to do automatically in mano cells. Tks a lot
2 Replies

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.




@Ingeborg Hawighorst 

Many 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



Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
4 Replies