Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
hello,
I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
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. "Joel" wrote: 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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
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") "Tyrone" wrote: 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. "Joel" wrote: 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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
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 "Joel" wrote: 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") "Tyrone" wrote: 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. "Joel" wrote: 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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
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 "Tyrone" wrote: 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 "Joel" wrote: 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") "Tyrone" wrote: 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. "Joel" wrote: 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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
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. "Joel" wrote: 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 "Tyrone" wrote: 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 "Joel" wrote: 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") "Tyrone" wrote: 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. "Joel" wrote: 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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
texttocolumn, limit the number of used columns to 1
hi again,
I've read lots of tutorials regarding vba. now I can implement this code properly, thanks for your help. regards. "Joel" wrote: 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 "Tyrone" wrote: 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 "Joel" wrote: 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") "Tyrone" wrote: 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. "Joel" wrote: 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. "Tyrone" wrote: hello, I have a list of datanames with one or more extensions. is it possible to perform one split only? I should look like: --------------------- hello|abc.def instead of: --------------------- hello|abc|def thanks in advance. greetings |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal - Limit in the number of columns? | Excel Discussion (Misc queries) | |||
Is there a limit in the number of columns that can be grouped? | Excel Discussion (Misc queries) | |||
TextToColumn Split | Excel Programming | |||
How do I limit number of rows and columns on a spreadsheet | New Users to Excel | |||
Increase the limit to number of columns? | Excel Discussion (Misc queries) |