What CHR code is Alt + Enter?

[bmcvicker]

Hi there,

I've been struggling for a few hours now trying to sort this problem. I have multiple rows of data that have been entered into excel cells using Alt + Enter. This has created a situation where I have multiple rows in each cell. I need to separate these out and I've tried various extraction methods but none of them (Text to Columns, Replace or VBA) seem to recognise the character that Alt + Enter is using.
It works, if I use a modified excel formula for finding first word, last word, nth word etc from a cell and I insert an Alt + Enter for the character I want to find. For the nth word I need to add a whole section to the formula and it is getting too cumbersome especially with Alt + Enter breaks through it and up to 15 rows in a cell. There is a VBA function that could do the trick (replacing the spaces it looks for with Alt + Enters) but it doesn't recognise the Alt + Enters as Chr(13)'s or vbCrLf's in the spreadsheet.


Any ideas on how I can find these Alt + Enters in VBA and use them in the function or replace them so I can separate this data out?


Thanks,
Brian

 

[Blade Hunter]

13 I believe

Edit: Make that 10

Go into Excel, enter it into a cell

Type this in the debug window of the VBE:

?asc(activecell.Text)

Excel can recognise this in formulas too:

=SUBSTITUTE(A1,CHAR(10),"") will replace the Alt-Enter's with nothing.

 

[Andrew Fergus]

Hi and welcome to MrExcel!

Alt+Enter = character 10.

To substitute these out of the text using an Excel formula, try this:
=SUBSTITUTE(A1,CHAR(10),",")
where the raw data is in cell A1.

Andrew

 

[bmcvicker]

Thanks Andrew, CHAR(10) worked in the formula.


Cheers,
Brian

 

[Peter_SSs]

Brian

You can do it directly with Text To Columns - see steps 1 and 2 of my post here:

http://www.mrexcel.com/forum/showpost.php?p=1690843&postcount=10