Text to column for File Name

[SH_94]

Hi Community ,

I would like to ask how can i perform the separation on the filename as per screenshot below.I was unable to separate it by using delimiter " ". May i know what are the formula that i need to input in this case?

Many thanks for your help.

[Luke_C]

A space delimiter (" ") or '\s' should work in the text to columns tool (it wouldn't remove the .xlsx but that's easy enough to get rid of). Tough to say why that's not working with just a screenshot of the data, if possible can you provide more info such as the configuration in your text to columns tool, or sample data/workflows?

Regex or text parsing formulas would also work, but you would need to confirm that the file names will always be in that format (2 letters, 3 letters, 4 numbers) for a sustainable approach.

[SH_94]

Hi @Luke_C ,

Kindly refer to the screenshot below for your reference. I have also attached the excel file on the desired output for your reference.

[Luke_C]

just type a space, no need for the quotes. Or enter \s which does the same thing as a space. 

[SH_94]

Hi @Luke_C ,

I had tried the method but it looks like still the same. As the number 7549 will always still together with xlsx . The result will be EM   HUN    7549.xlsx.

Do you have any idea on how to fix this?

[Luke_C · Accepted solution]

You can use a replace function. See attached with an example of that. It also includes a regex example as well. 

[SH_94]

Hi @Luke_C ,

Thank you so much for the sharing. It is working now.

Thanks again.