15 Digit Sequence Pt. 2

[ChompGator]

Hello,

I have a previous topic called '15 Digit Sequence' In the topic I marked it as 'Solved' in my last post, and Ive been told by regular forum posters not to post in it once it's solved and just open a new topic and provide a link to the old topic. The old thread is located here
http://www.mrexcel.com/forum/showthread.php?t=381233

My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is:
=IF(MID(C378,LEN(C378)-1,1)="W",C378&" ,",C378)

And what that does is it would change data in a cell that looks like this
01-02-034-05-W2 TO 02-03-045-06-W2

To this:
01-02-034-05-W2 TO 02-03-045-06-W2 ,


The formula works great, thanks to the help of the posters!

However I just ran into an issue, what if I have a set of data in a cell that looks like this:
01-02-034-05-W2 TO 02-03-045-07-W2
02-02-034-05-W2 TO 02-03-045-07-W2
03-02-034-05-W2 TO 02-03-045-07-W2

And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied:

01-02-034-05-W2 TO 02-03-045-07-W2 ,
02-02-034-05-W2 TO 02-03-045-07-W2 ,
03-02-034-05-W2 TO 02-03-045-07-W2 ,

Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.

Thanks!

 

[arkusM]

What is the source of data that puts three line of well UWI's in one cell?
Are they entered manually? Do they have to be entered that way?

 

[Mike Blackman]

Hi Again,

I cant see any reason why Andrew's formula wouldn't work for those results. Is it simply not adding the space and the comma?

 

[ChompGator]

Hey,

What is the source of data that puts three line of well UWI's in one cell?
Are they entered manually? Do they have to be entered that way?

A user has entered them all manually into one cell. As far as I know there isn't going to be any more entered, so its just a matter of getting the comma and space entered.

 

[ChompGator]

Hi Again,

I cant see any reason why Andrew's formula wouldn't work for those results. Is it simply not adding the space and the comma?

Hey Mike,

Its adding the space a comma now...

However if there more than 1 number sequence in a cell (such as)

01-02-034-05-W2 TO 02-03-045-07-W2
02-02-034-05-W2 TO 02-03-045-07-W2
03-02-034-05-W2 TO 02-03-045-07-W2

The formula won't apply a comma and space when the data appears in one cell like seen above.

But like mentioned above if there is only 1 sequence in a cell like this:
01-02-034-05-W2 TO 02-03-045-07-W2

the formula works, it just doesn't seem to work when there are multiple instances of the data set in 1 cell

 

[arkusM]

Hey,


A user has entered them all manually into one cell. As far as I know there isn't going to be any more entered, so its just a matter of getting the comma and space entered.

How many cells are you dealing with?
IF there are not a lot then manually may be the best option.
IF there are a lot then modifying the formula is possiable.
If it is recurring then VBA would probablt be the best method.

 

[Mike Blackman]

How about;

=IF(MID(TRIM(C378),LEN(TRIM(C378))-1,1)="W",C378&" ,",C378)

Just wondering if there are additional spaces at the end of the text causing an issue, Andrews formula would work regardless of lengh.

Maybe try;

=MID(C378,LEN(C378)-1,1)

And see which character is returned for me.

 

[ChompGator]

How many cells are you dealing with?
IF there are not a lot then manually may be the best option.
IF there are a lot then modifying the formula is possiable.
If it is recurring then VBA would probablt be the best method.

Hey Arkus,

Thats what I was just considering, if VBA would be the way to go here.

Total, we're looking at about 100,000 lines. Do you think altering the formula is possible?

 

[ChompGator]

How about;

=IF(MID(TRIM(C378),LEN(TRIM(C378))-1,1)="W",C378&" ,",C378)

Just wondering if there are additional spaces at the end of the text causing an issue, Andrews formula would work regardless of lengh.

Maybe try;

=MID(C378,LEN(C378)-1,1)

And see which character is returned for me.

Hey Mike,

Sounds good, Ill give it a shot and let you know the results.

 

[arkusM]

Hey Arkus,

Thats what I was just considering, if VBA would be the way to go here.

Total, we're looking at about 100,000 lines. Do you think altering the formula is possible?

Formula is probably possible.
You need to understand that you data is not
01-02-034-05-W2 TO 02-03-045-07-W2
02-02-034-05-W2 TO 02-03-045-07-W2
03-02-034-05-W2 TO 02-03-045-07-W2

but
01-02-034-05-W2 TO 02-03-045-07-W2 02-02-034-05-W2 TO 02-03-045-07-W2 03-02-034-05-W2 TO 02-03-045-07-W2

Then you have to chip at the formula.
I'll give er a go.
Do you need the results to be 3 to a cell like it is now or one to a cell?