Excel INSTR to find hard return in cell

Question

I have a spreadsheet where the Item description is a variable length but always finishes with a "hard return" to force a wrapped line in the cell. I need to copy the first line of the cell into another cell.

Can I use INSTR to find the first instance of a hard return (invisible character) and then copy the first N-1 characters?

For example:

Dell PowerEdge R720XD
Chassis (Max of ......

OR

Dell OptiPlex 7010 Minitower   
Intel Core.............

In all cases I need to copy the first line of the text in the cell, irrespective of length.

Any ideas how I could do this??

Answer 1

Yes, you can easily do this

strShort = Left(strLong, InStr(strLong, vbCrLf) - 1)

Some times (eep. when sourced from a Unix system), you might have to replace vbCrLf (carriage Return, LineFeed) with a vbLf only.

If you are not sure if it contains an Enter, this code will do

strShort = IIf(InStr(strLong, vbCrLf), Left(strLong, InStr(strLong, vbCrLf) - 2), strLong)