texttocolumn, limit the number of used columns to 1

[Guest]

hello,
I have a list of datanames with one or more extensions. is it possible to
perform one split only? I should look like:

 

[Guest]

It is treating the period as a wildcard. One thing you can do is replace the
period with the characters ZZ. Do the split. Then replace ZZ with a period.

 

[Guest]

hi Joel,
thank you for your quick reply.
how do you code this? i just started with vba and don't have a clue yet.

regards.

 

[Guest]

I don't have a clue at what you are really trying to do. the word split can
mean a lot of different things. My firstt impression you werre using some
form of Test-to-columns or importing data. I'm now thinking you are just
spliting a string

for spliting strings

left_piece = left(mystring,instr(mystring,"|") - 1)
right_piece = mid(mystring,instr(mystring,"|") + 1)


for replacement
new_string = replace(mystring,"|","ZZ")

 

[Guest]

ok, sorry for that. i try to explain the situation:
i have 4 columns in total:
Filename (Drive C | Extension | Filename (Drive D | Extension

now I have to sort a lot of filenames according to their extension and
alphabetically. some of the files have two or more extensions and they
overwrite the filenames on the column "drive d:" . I hope this makes it clear.

thank you for your patience.
greetings

 

[Guest]

This code will split a filename including a patth into thhe three pieces you
need.

Sub testr()
Myfilename = "c:\temp\joel\xyz\abc.txt"

MyPath = ""
Do While InStr(Myfilename, "\") > 0
MyPath = MyPath & Left(Myfilename, InStr(Myfilename, "\"))
Myfilename = Mid(Myfilename, InStr(Myfilename, "\") + 1)
Loop

MyExtension = Mid(Myfilename, InStr(Myfilename, ".") + 1)
Myfilename = Left(Myfilename, InStr(Myfilename, ".") - 1)
End Sub

 

[Guest]

well, i think i am too stupid too implement this code properly. there is no
error alert , but nothing is happening. would it be possible to use this code
on a selected column with the range-syntax, where the files had already been
listed?

thank you for your help.

 

[Guest]

hi again,
I've read lots of tutorials regarding vba. now I can implement this code
properly, thanks for your help.

regards.