SOLVED
Home

Possibility to back return in an Excel cell.

%3CLINGO-SUB%20id%3D%22lingo-sub-878196%22%20slang%3D%22en-US%22%3EPossibility%20to%20back%20return%20in%20an%20Excel%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878196%22%20slang%3D%22en-US%22%3EHow%20to%20write%20a%20procedure%20or%20formula%20to%20have%20in%20several%20cells%20a%20text%20that%20After%20a%20comma%20or%20others%2C%20return%20back%20starting%20a%20new%20ore%20in%20the%20same%20cell.%20This%20is%20possibile%20with%20ALT%20%2B%20Enter%2C%20but%20I%20need%20to%20do%20automatically%20in%20mano%20cells.%20Tks%20a%20lot%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-878196%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878326%22%20slang%3D%22en-US%22%3ERe%3A%20Possibility%20to%20back%20return%20in%20an%20Excel%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878326%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383420%22%20target%3D%22_blank%22%3E%40antonio1958%3C%2FA%3E%26nbsp%3B%2C%20you%20tagged%20this%20question%20with%20%22Get%20and%20Transform%22.%20Does%20that%20mean%20you%20want%20to%20do%20that%20with%20Power%20Query%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20do%20a%20find%20and%20replace%20to%20replace%20a%20comma%20with%20the%20Ascii%20character%2010.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20do%20this%20with%20VBA%2C%20you%20can%20use%20code%20along%20these%20lines%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0AIf%20Not%20Intersect(Target%2C%20Range(%22A%3AC%22))%20Is%20Nothing%20Then%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20Target.Replace%20What%3A%3D%22%2C%20%22%2C%20Replacement%3A%3D%22%22%20%26amp%3B%20Chr(10)%20%26amp%3B%20%22%22%2C%20LookAt%3A%3DxlPart%2C%20_%0A%20%20%20%20%20%20%20%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20_%0A%20%20%20%20%20%20%20%20ReplaceFormat%3A%3DFalse%2C%20FormulaVersion%3A%3DxlReplaceFormula2%0A%20%20%20%20Target.WrapText%20%3D%20True%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20If%0A%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20code%20looks%20at%20columns%20A%20to%20C.%20When%20a%20cell%20in%20these%20columns%20is%20edited%2C%20any%20comma%20followed%20by%20a%20space%20will%20be%20replaced%20with%20a%20new%20line%20and%20the%20cell%20will%20be%20formatted%20to%20wrap%20text.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20use%20this%20code%2C%20right-click%20on%20the%20sheet%20tab%20and%20select%20%22View%20Code%22.%20That%20will%20open%20the%20Code%20Editor%20Paste%20the%20code%20into%20the%20big%20code%20window%20area.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878911%22%20slang%3D%22en-US%22%3ERe%3A%20Possibility%20to%20back%20return%20in%20an%20Excel%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Tks%20Ingeborg%3C%2FP%3E%3CP%3EYes%20I%20was%20looking%20at%20possibility%20to%20use%20Power%20Query%20functionality%20like%20split%20columns%20in%20row%2C%20but%20it%20is%20not%20the%20right%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20it%20all%20comes%20from%20a%20Power%20Pivot%20where%20I%20used%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCATENATEX(Range%3B%5BGeneric%20Job%20Profile%20and%20code%5D%3B%22%3B%20%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20have%20values%20in%20Pivot%20field.%20And%20The%20values%20(Job%20Profile)%20that%20is%20Text%2C%20is%20only%20separate%20by%26nbsp%3Ba%20semicolon%20I%20added%20in%20the%20formula.%3C%2FP%3E%3CP%3EMay%20be%20bis%20possible%20to%20modify%20this%20formula%20to%20get%26nbsp%3BJob%26nbsp%3BProfile%20followed%26nbsp%3Bby%20a%20semicolon%20in%20differerent%20row%20in%20the%20same%26nbsp%3Bcell%20%3F%20I%20do%20manually%20with%20ALT%2BENTER.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETks%20a%20lot%3C%2FP%3E%3CP%3EAntonio%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
antonio1958
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.

 

 

Solution

@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

Antonio  

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies