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,

Thanks for the response, the strings are seperated by Alt+Enter.
(Let me know if you require anymore information).

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.

 

[tusharm]

I just reviewed the other discussion and am at a loss as to why you are testing for W as the last but 1 character. From what I can see, the following should do the job.

=SUBSTITUTE(C17,CHAR(10)," ," &CHAR(10))&" ,"
where C17 is the original cell.

I imagine one could include the IF test but I am at a loss as to why.

 

[ChompGator]

Hey Tusharm,

Thanks for your post, I hate to ask this, but would you be able to explain what you mean a little more?

Ill expand on why Im doing what Im doing...The sequence of numbers are called ""UWIs"" and I have sets of UWIs that appear in my in my excel sheet that look like this: (note this is all in one cell, not 4 individual cells, but 1 cell)
01-04-007-21-W4 TO 01-04-007-08-W4
01-04-007-22-W4 TO 01-04-007-08-W4
01-04-007-23-W4 TO 01-04-007-08-W4
01-04-007-24-W4 TO 01-04-007-08-W4

The reason why they need to have a space then comma at the end is because eventually the Excel sheet will be uploaded into a new piece of software, and that software is only programmed to recognize a new set of data once a comma and space appears. So for instance, the new piece of software will inperpret the above data as one big piece of information, instead of interpreting each set of UWI's as a different range...So what I mean by range is:
This is one range UWI's
01-04-007-21-W4 TO 01-04-007-08-W4

This is another range of UWI's
01-04-007-22-W4 TO 01-04-007-08-W4

This is another range of UWIs
01-04-007-23-W4 TO 01-04-007-08-W4

However those ranges all appear in one cell like this:

01-04-007-21-W4 TO 01-04-007-08-W4
01-04-007-22-W4 TO 01-04-007-08-W4
01-04-007-23-W4 TO 01-04-007-08-W4


So as mentioned when we upload this to the new software the software wil inperpret all the numbers as one big set of data, so thats why we need to add the space then comma after the last "W4" on each line - That way eventually when this excel sheet gets uploaded to the new software the software can understand that this is one range
01-04-007-21-W4 TO 01-04-007-08-W4 , this is the second range 01-04-007-22-W4 TO 01-04-007-08-W4 , this is the third range 01-04-007-23-W4 TO 01-04-007-08-W4 ,

It sounds allot more complex than it is (I think) - Because so far with the help of Mike and Arkus - Ive got a formula that can add a space and comma if there is only 1 UWI in a cell, but when multiple UWIs appear in one cell then the formula doesn't work.

Oh and one additional piece of information is it won't always be "W4" in some cases it might be "W5" or "W6" or "W2" or "W1" - but there will always be a "W" then a number (Ie: W4)

Any thoughts?

 

[tusharm]

Tested my suggestion? It should do what you want.

Hey Tusharm,

Thanks for your post, I hate to ask this, but would you be able to explain what you mean a little more?

Ill expand on why Im doing what Im doing...The sequence of numbers are called ""UWIs"" and I have sets of UWIs that appear in my in my excel sheet that look like this: (note this is all in one cell, not 4 individual cells, but 1 cell)
01-04-007-21-W4 TO 01-04-007-08-W4
01-04-007-22-W4 TO 01-04-007-08-W4
01-04-007-23-W4 TO 01-04-007-08-W4
01-04-007-24-W4 TO 01-04-007-08-W4

The reason why they need to have a space then comma at the end is because eventually the Excel sheet will be uploaded into a new piece of software, and that software is only programmed to recognize a new set of data once a comma and space appears. So for instance, the new piece of software will inperpret the above data as one big piece of information, instead of interpreting each set of UWI's as a different range...So what I mean by range is:
This is one range UWI's
01-04-007-21-W4 TO 01-04-007-08-W4

This is another range of UWI's
01-04-007-22-W4 TO 01-04-007-08-W4

This is another range of UWIs
01-04-007-23-W4 TO 01-04-007-08-W4

However those ranges all appear in one cell like this:

01-04-007-21-W4 TO 01-04-007-08-W4
01-04-007-22-W4 TO 01-04-007-08-W4
01-04-007-23-W4 TO 01-04-007-08-W4


So as mentioned when we upload this to the new software the software wil inperpret all the numbers as one big set of data, so thats why we need to add the space then comma after the last "W4" on each line - That way eventually when this excel sheet gets uploaded to the new software the software can understand that this is one range
01-04-007-21-W4 TO 01-04-007-08-W4 , this is the second range 01-04-007-22-W4 TO 01-04-007-08-W4 , this is the third range 01-04-007-23-W4 TO 01-04-007-08-W4 ,

It sounds allot more complex than it is (I think) - Because so far with the help of Mike and Arkus - Ive got a formula that can add a space and comma if there is only 1 UWI in a cell, but when multiple UWIs appear in one cell then the formula doesn't work.

Oh and one additional piece of information is it won't always be "W4" in some cases it might be "W5" or "W6" or "W2" or "W1" - but there will always be a "W" then a number (Ie: W4)

Any thoughts?

 

[arkusM]

What I think is missing is the data appears like this:
in cell A2:
01-04-007-21-W4 TO 01-04-007-08-W4 01-04-007-22-W4 TO 01-04-007-08-W4 01-04-007-23-W4 TO 01-04-007-08-W4 01-04-007-24-W4 TO 01-04-007-08-W4
it is only formated to wrap into four lines

 

[ChompGator]

Tested my suggestion? It should do what you want.

Hey,

So right now Im testing on cell C3, so I changed the current cell C17 so C3 - and it returned a result of 0...Unfortunately thats all it did

 

[mooseman]

ChompGator,
If you put this modified formula in cell D1 and then copy it down and all your data is in column C, this will remove all the Alt+Enter and replace it with a space and a comma. It will add a space and comma to the end of the string if there is no Alt+Enter (There would be only one string in that cell then (right?)

=SUBSTITUTE(C1,CHAR(10)," ,")&" ,"

tusharm wrote the formula correctly, but I didn't think you want the Alt+Enter anymore.

 

[arkusM]

OK ChompGator give this a go.
You will need to adjust your columns as I tested in Col B and put the results in Col C.
This assumed that the UWI's are in one column and continous ie no spaces.

Sub ADD_the_commas()
Dim i As Integer
Dim r As Range
Dim lToFind As Long, xi As Long
Dim lWFind As Long
Dim myString1 As String
    i = 1
Debug.Print "*****************************************"
    For Each r In Range("B1", Range("B" & Rows.Count).End(xlUp))
    Debug.Print "r "; r
        For xi = 1 To 20
            lToFind = InStr(i, r, "to", 1)    ' Looks for the "to" in the string
                Debug.Print "To"; lToFind     ' For debugging purposes
                If lToFind = 0 Then Exit For  ' When it can't find the "to" or it has run out of "to"'s it exits
            lWFind = InStr(lToFind, r, "W", 1) + 1 ' Finds the "W" after the "To"
                Debug.Print "W"; lWFind
            i = lWFind
                Debug.Print "i"; i
            myString1 = myString1 & Mid(r, (i - 33), 34) & ", " ' Puts a comma after the string
                Debug.Print "myString1 "; myString1
                
            Debug.Print "----------------------------"
        Next xi
       
    r.Offset(0, 1).Value = myString1 'puts the results in Col C (in Col over)
    myString1 = ""
    i = 1
    Next r
End Sub

 

[ChompGator]

Hey,

Code Syntax makes sense, however I applied the VBA to my sheet in Excel and it deleted all my UWI's lin column B

 

[ChompGator]

Now Im trying to run the code again, and it's giving me a
Run Time Error 5 Invalid Procedure Name or Argument