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!

 

[ChompGator]

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?

Hey Mike,

So for cell 378, when applying
=MID(C378,LEN(C378)-1,1)

The number 4 gets returned.

When trying the
=IF(MID(TRIM(C378),LEN(TRIM(C378))-1,1)="W",C378&" ,",C378) formula

It does what Andrew's does and just add's the comma and space, which is what I want, but there are several thousand lines where the text is wrapped inside one cell and inside the one cell there the numbers appear like this

01-02-003-07-W2 TO 01-05-006-07-W2
01-03-002-04-W2 TO 01-02-003-04-W2

I think maybe Im not explaining myself well enough (sorry!)

Andrews formula does work when only one number sequence appears in a cell so if this appears in A1
01-02-003-07-W2 TO 01-05-006-07-W2

The formula will work and apply the comma and the space, but say instead of that number in A1 you have a number set like this:

01-02-003-07-W2 TO 01-05-006-07-W2
01-03-002-04-W2 TO 01-02-003-04-W2

Well in every cell where the numbers are listed like whats above the formula wont add a space and comma, not sure why
Maybe because the data is wrapped? Im not sure, any thoughts?

Thanks!

 

[ChompGator]

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?

Hey Arkus, that makes sense!
Well in some cells there are 8 instances of UWI's, in other cells there is only 3 instances, in other cells there are just two instances.

So it varies depending on the cell. Did that answer your question?

 

[Mike Blackman]

So would mine not just work for all of them regardless? If there is nothing to TRIM then it wont TRIM, if there is it will?

 

[arkusM]

Not really, but it certianly changes the problem....
That much of variablity will pretty much require VBA to my mind.
My question is do you need the end result (up to 8 UWI's) with comma's in one cell like they are currently or in individual cells?
If this data for upload into another system? What is end purpose for the data?

 

[ChompGator]

Not really, but it certianly changes the problem....
That much of variablity will pretty much require VBA to my mind.
My question is do you need the end result (up to 8 UWI's) with comma's in one cell like they are currently or in individual cells?
If this data for upload into another system? What is end purpose for the data?

Hey Arkus,

The end result should place comma's and the space in the one cell where the numbers are currently. The purpose of the data at this point is just for reference, but eventually (in around 9 months time) the data will be uploaded into another system, and the comma and space that needs to be added is the way the new system will interpret the difference between the ranges.

IE: By adding the comma and space the new software is setup to understand that
01-02-003-04-W1 TO 01-03-004-05-W1, is seperate from
02-02-034-05-W2 TO 02-03-045-07-W2
because of the use of a comma and space.

Does that help?

 

[arkusM]

Yup. Thanks. I am pretty new with VBA so this migh be a while. I am trying to adapt one of the routines that I currently run to help. Maybe a VB master will see this and help out too!!

 

[ChompGator]

So would mine not just work for all of them regardless? If there is nothing to TRIM then it wont TRIM, if there is it will?

Hey Mike, it only seems to work if there is only 1 sequence of numbers in 1 cell, when multiple sequences appear one after the other it doesn't seem to work
I posted a working example for you at
http://www.calgarynearsite.ca/smple.xls

 

[ChompGator]

Yup. Thanks. I am pretty new with VBA so this migh be a while. I am trying to adapt one of the routines that I currently run to help. Maybe a VB master will see this and help out too!!

Hey Arkus, thats fantastic!

Thanks for the help, Ill await your response!

 

[arkusM]

did you get my PM?

 

[tusharm]

How are the strings separated? Is it a space character? Or a 'CR/LF' character (typed with the Alt+ENTER key combination)?

The easiest way to find out -- I think -- is to increase, probably double, the column width. If it is a space character you will find the 2 'rows' become one. If it is the CR character, they will remain 2 rows.

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!