adding a line break after ***, extension

[exceled]

Hi everyone... first question, go steady on me!!

I want to add a line break to separate files in one cell

Example:

testfile.doc,second testfile.pdf,third,testfile.doc

Result:

testfile.doc
second testfile.pdf
third,testfile.doc

all within the same cell, any ideas?

 

[VoG]

Maybe like this. You need to set the result cell to Wrap Text.

Excel Workbook
	A	B
1	testfile.doc,second testfile.pdf,third,testfile.doc	testfile.docsecond testfile.pdfthirdtestfile.doc
Sheet5

 

[exceled]

Thanks for your help, however thats the best I came up with.

If the file name has a comma in it, it doesn't work:

For example

123 34, 123, 34, 34 - test.doc becomes

123 34
123
34
34 - test.doc

which isn't great

 

[Rick Rothstein]

Thanks for your help, however thats the best I came up with.

If the file name has a comma in it, it doesn't work:

The problem is you picked a bad character (the comma) to use as a delimiter because it is a valid character in file name. Why don't you use, say, an asterisk or maybe a question mark (wildcard symbols are not allowed in a filename) as your delimiter.

 

[exceled]

I know,

I have just been given the document in that format with over 3000 files.

 

[Rick Rothstein]

Do you know what all of the possible extensions for your filenames could be? If so, can you post a list of them for us? If not, can you assure us that none of the filenames have dots in them (except for the one in front of the extension, that is)?

 

[exceled]

Thanks for your help,

the only file name extensions are:
.doc
.ppt
.PDF (.pdf)
.TIF
.docx

 

[Rick Rothstein]

the only file name extensions are:
.doc
.ppt
.PDF (.pdf)
.TIF
.docx

Does this UDF do what you want?

Function CreateLF(S As String) As String
  Dim X As Long, V As Variant, Extensions() As String
  Extensions = Split(".doc .ppt .pdf .tif .docx")
  CreateLF = S
  For Each V In Extensions
    CreateLF = Replace(CreateLF, V & ",", V & vbLf, , , vbTextCompare)
  Next
  Do While InStr(CreateLF, vbLf & " ")
    CreateLF = Replace(CreateLF, vbLf & " ", vbLf)
  Loop
End Function

Note: Don't forget to format the cells you put this formula in to "Wrap text" (Alignment tab of the Format Cells dialog box).
.

 

[exceled]

Perfect....

Very impressed!!!