VBA search for characters+hard return

[Dabenic]

Hello all!

I have a series of cells I need to work on and separate the contents based on criteria. The common characteristic marking the point where I need to separate text is two commas followed by a hard return.

How can I format the search criteria so it looks for ",," plus the hard return (vbLf)?


Thank you!

 

[kweaver]

Welcome to Mr. Excel.

It might help if you had examples of your data and expected results.

Do you mean this:

=LEFT(A2,FIND(",,"&CHAR(10),A2)-1)

and

=RIGHT(A2,LEN(A2)-FIND(",,"&CHAR(10),A2)-1)

with your text in A2 ??

 

[Micron]

Instr function will find ,,
I think you mean vbCrLf? That is likely a combination of chr(13) and chr(10) since vbCrLf is a vba constant and not an actual character.

VBA requested in thread title so that's the basis for my suggestion but agree - sample input and output would help to clarify.

 

[Dabenic]

Thank you for your quick responses.

Here is an example of what has to happen. This is data that is extracted from a SaaS platform and has to be imported into another. The two circled items are what are key to determine where to separate the data.

 

[Micron]

Still don't get it. The before and after look exactly the same.
You have 2 suggestions so far - maybe try one or both?
My first answer applies to this

How can I format the search criteria so it looks for ",," plus the hard return (vbLf)?

I know what it means to "separate" but not in your case.

 

[Dabenic]

Still don't get it. The before and after look exactly the same.
You have 2 suggestions so far - maybe try one or both?
My first answer applies to this

I know what it means to "separate" but not in your case.

In the first sample, you have 3 sets of data that have been merged into a single cell.
In the second, that data has been separated into three separate rows, using ",," before a hard return as the determining factor. There may be other hard returns within the raw data, but only hard returns after two commas are relevant.

 

[Micron]

D'oh! That's the problem with pictures of data instead of pasting actual data. My bad.

 

[Micron]

OK, so this seems to find line feed character (10) in N5 on sheet2. I guess carriage return (13) doesn't apply in this case.

instr(Sheets("Sheet2").range("N5"),chr(10))

Am I correct in assuming that the ",," doesn't matter because it's followed by a line feed? If it does, then perhaps
instr(Sheets("Sheet2").range("N5"),",," & chr(10))

 

[Dabenic]

OK, so this seems to find line feed character (10) in N5 on sheet2. I guess carriage return (13) doesn't apply in this case.

instr(Sheets("Sheet2").range("N5"),chr(10))

Am I correct in assuming that the ",," doesn't matter because it's followed by a line feed? If it does, then perhaps
instr(Sheets("Sheet2").range("N5"),",," & chr(10))

The ",," is important. There may be other hard returns within the cell. Only the hard returns happening after a ",," mark a distinct set of data.

For context, these fields contain customer notes from one database. The notes are entered/updated at different times by different people - some use hard returns others don't. The way the data is extracted, for some reason, some of the customer notes get merged with other customer notes, and this has to be cleaned up before it gets imported into the new database. It can take one person 5-8 hours to go through one file with thousands of records. The only consistent thing is that the merged fields are separated by two commas followed by a hard return. Hence the importance of those two commas.

 

[Micron]

Hopefully the revised expression will work then. Not sure if you realize that it's only one tiny piece of the puzzle. That alone isn't going to do much.
Post 6 here has a Do Loop that parses portions of a string into different cells (vertically) if you need it. Your test would be along the lines of
If expressionHere > 0 Then
If it's not > 0 the function did not find ,,&linefeed character.