6 sectioned file name using 2 spaces to separate each section - needing 6 formulas to enter each section into 6 separate cells.

[alexa9622]

HI!

I have file names automatically generated in File Juggler. It uses OCR to capture the information and renames the files in 6 sections which are each separated by 2 spaces. The 6 sections are never the same number of characters. I have coworkers who do not know excel but can copy and paste info into one cell (find and replace is not an option LOL). The spreadsheet would be opened and new file names entered regularly.

If I paste the file name OPT-20 Staples V#2400 3445370000 2020-04-18 VN 86.31 into cell A2, what formula can I put into cells B2 C2 D2 E2 F2 G2 to divide the info into the correct cell? I have been able to do 3 columns in the past, but I cannot figure out how to do 6 columns. Unfortunately, I do not know how to code.... yet =)

Thank you in advance for your help!

 

[Peter_SSs]

what formula can I put into cells B2 C2 D2 E2 F2 G2

Try this in B2, copied across. Note that inside the red quote marks are two spaces. Inside the blue quote marks, just one space.

=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",100)),COLUMNS($B:B)*100,100))

21 09 16.xlsm
	A	B	C	D	E	F	G
2	OPT-20 Staples V#2400 3445370000 2020-04-18 VN 86.31	OPT-20	Staples V#2400	3445370000	2020-04-18	VN	86.31
Split file name

Cell Formulas
Range	Formula
B2:G2	B2	=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",100)),COLUMNS($B:B)*100,100))

 

[alexa9622]

Oh my goodness! I love you! Thank you so much!!! It worked like a charm!!!

You do not know how much work you have saved all the department heads in a small children's hospital! I cannot express my gratitude enough!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.