Find then remove Alt+Enter in cells

ubercuda

New Member
Joined
Sep 14, 2014
Messages
2
Hi All!

I have a spreadsheet (Excel 2016 on Win 10) where certaincells in a column have multiple values entered using the Alt+Enter keycombination in a single wrapped cell. Ineed to extract those values and create separate cells for furtherprocessing. In a nutshell:

“J26” = 12
20
6
7

Needs to become:

“J26” = 12
“J27” = 20
“J28” = 6
“J29” = 7

With the original “J26” deletedand all other cells moved up/down as needed to preserve the data. I have tried recording a macro but all itshows is the end result being entered into the cells as a formula – does notshow what was removed to get there. Ihave searched for many hours but still do not have enough information to evenattempt writing the code.

Thank you so much for any assistanceand expertise!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,143
try this on a copy of your file

Code:
Sub split_it()

data = Range("K26")
For x = 0 To 3
Cells(x + 26, "K") = Split(data, vbLf)(x)
Next x

End Sub

hth,

Ross
 

ubercuda

New Member
Joined
Sep 14, 2014
Messages
2
Thanks Ross! Fortunately, I was able to pull some cotton out of my head and realized I could use the "WorksheetFunction.Substitute" method to change all the unwanted characters to simply spaces. I can use simple string manipulation from there. The code that works is:

NewString = WorksheetFunction.Substitute(TestStr, Chr(10), Chr(32))

The result is a new string that has all the individual cell values separated by a single space. No more carriage returns or line feeds (not sure where the Chr(13) carriage returns went, but they are gone.) Thus the cell that had:

"12
20
6
7"

now has: "12 20 6 7"

No leading or trailing spaces (though none were there originally).

Best wishes to all!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,680
Members
414,008
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top