# 15 Digit Sequence Pt. 2

#### ChompGator

##### Board Regular
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

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!

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### arkusM

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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.

Last edited:

#### ChompGator

##### Board Regular
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

Last edited:

#### arkusM

##### Well-known Member
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

##### Well-known Member

=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

##### Board Regular
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

##### Board Regular

=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

##### Well-known Member
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?

Replies
5
Views
218
Replies
5
Views
414
Replies
0
Views
79
Replies
16
Views
388
Replies
4
Views
392

1,191,286
Messages
5,985,749
Members
439,979
Latest member
alekun86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back