CODEWITHSUNDEEP
excelvba
Keith Graham
Keith Graham

Reputation: 61

Excel INSTR to find hard return in cell

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??

Upvotes: 6

Views: 33958

Answers (4)

Jan
Jan

Reputation: 1

my solution is:

strString = Replace(strString, vbLf, "")

It is useful even if the string contains more than one line brakes.

Upvotes: -1

awsmitty
awsmitty

Reputation: 121

I was not able to add a comment to Ripster's code below. I needed a reputation of 50; mine's only 36, but that's really where this comment should go instead of a separate answer.

It seems to me that the last bit of Ripster's code,

Right(Range("A1").Text, InStr(Range("A1").Text, vbLf))

should be,

Right(Range("A1").Text, (Len(Range("A1")-InStr(Range("A1").Text, vbLf)) - 1)

And finally to regenerate the string without the break

strString = Left(Range("A1").Text, InStr(Range("A1").Text, vbLf) - 1) & Right(Range("A1").Text, (Len(Range("A1")-InStr(Range("A1").Text, vbLf)) - 1)

A note to the moderators : Please review this. I still consider mysefl as a beginner to VBA and my logic could be wrong.

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17475

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)

Upvotes: 6

Ripster
Ripster

Reputation: 3585

Depending on the type of line break you can do it this way:

InStr(Range("A1").Text, vbLf)
InStr(Range("A1").Text, vbCr)
InStr(Range("A1").Text, vbCrLf)

to get the text before the line break:

Left(Range("A1").Text, InStr(Range("A1").Text, vbLf) - 1)

to get the text after the line break:

Right(Range("A1").Text, InStr(Range("A1").Text, vbLf))

Upvotes: 2

Related Questions