Text Formulas - Need Assistance

[Josiev]

Hello,

I need assistance on what proper text formulas to use to separate different parts of one code from one column. I need to update a table to get a list of codes that have 5 parts in it but are laid out in one column, separated by periods in between. (For example SBU0001.0.0.0.W22). However, the # of characters for each part may vary. Normally, I would copy this list from this column in a separate workbook and do a Text to Column function, then copy and paste the separated parts back to the original workbook. I was wondering if I can just plug in text formulas instead.
Attached is a sample workbook. Column A is the list I need to update - there are 5 parts in each code joined together but are separated by periods in between. I need to list each of the 5 parts from these codes on separate columns- columns B to F. Can a text formula do this function?
Any help from the experts is greatly appreciated.

 

[Haz]

Write the formula in B2, then copy and paste:
=INDEX(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",50)),{1,51,101,151,201},50)),COLUMNS($B2:B2))

 

[Josiev]

Write the formula in B2, then copy and paste:
=INDEX(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",50)),{1,51,101,151,201},50)),COLUMNS($B2:B2))

Hi Haz....Wow, this formula worked perfectly. I can't thank you enough - this will save me so much time, so efficient.

 

[pecoflyer]

Your technique of using text to columns works perfectly. Just use the period as separator

 

[Peter Bartholomew]

Just to prove to myself that I can solve the problem with named formulas!

I let 'string' be the original encrypted string, padded with additional separators and the start and end,
string: = "." & Sheet1!$A2 & "."

I let 'k' be an array of substring indices, which may be in any order,
k: ={5,2,3,4,1}

To determine the start of a substring requires SUBSTITUTE to mark the location and SEARCH to return the character number,
start: = 1 + SEARCH( "|", SUBSTITUTE( string, ".", "|", k ) )

The substring length is obtained by a SEARCH over the remainder of the string
length: = SEARCH( ".", MID(string, start, 100) ) - 1

With that level of setup effort, the formula on the worksheet is disarmingly simple:
= MID( string, start, length )

I have written the formula out as an array because I prefer array formulas to relative reference formulas but it would be equally easy (or some would say 'difficult') to pick the index 'k' up as a relative reference to the column heading (say) rather than using an array constant.