Delimit strings

[Ivhee]

Hi,

Can you help me to delimit the strings using formula. Below is the sample data

D:\Data\Personal\sramasam\desktop\Exceldata.xls	D	Data	Personal	sramasam	desktop	Exceldata.xls
D:\Personal\sramasam\desktop\Exceldata.xls	D	Personal	sramasam	desktop	Exceldata.xls

Thank you in advance.

[benishiryo]

hi Ivhee. assuming data in A1, try this in B1 & copy down & across:
=TRIM(MID(SUBSTITUTE($A1,"\",REPT(" ",100)),1+(COLUMNS($B1:B1)-1)*100,100))

i used "\" as the delimiter, so the first one has the ":". you can use a different formula in Column B or if you don't want ":" at all, apply to the rest as well:
=TRIM(SUBSTITUTE(MID(SUBSTITUTE($A1,"\",REPT(" ",100)),1+(COLUMNS($B1:B1)-1)*100,100),":",""))

[Mavenn]

I used this for when I had this issue, works great:

(Copied and pasted from "Knowledge-Paks")

To split delimited text into multiple cells:

1) Open Microsoft Excel worksheet.
2) Select the range of cells in a column on which you want to perform the split action.
3) Click the 'Data' tab. (The 'Data' tab appears.)
4) From the 'Data Tools' group, click the 'Convert Text to Table' icon. (The 'Convert Text to Columns Wizard' appears.)
5) Ensure that the 'Delimited' option is selected.
6) Click the 'Next' button. (The Delimiters screen appears.)
7) From the 'Delimiters' group, select the appropriate check box. (The Data preview appears according to the selected delimiter.)
8) Click the 'Next' button. (The Set data format screen appears.)
9) From the 'Column data format' group, select the appropriate option.
NOTE: You may select different cell location by entering appropriate range in the 'Destination' box.
10) Click the 'Finish' button. (The cell values splits into two columns.)

[Ivhee]

Hi,

I need to use formula instead of Text to Columns..

Thanks for all your help.

[martindwilson]

cant you just use text to columns it would be quicker

[Ivhee]

Hi Benishiryo,

Works great!! Thanks a lot for the help.


Thanks also Mavenn!