Find then remove Alt+Enter in cells

[ubercuda]

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!

 

[rpaulson]

try this on a copy of your file

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]

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!