To divide part of cells

Topic

New Reply

Hi all,

Kindly need some formula to separate text like below in a cell

311011-Team Review Panel-PR Core Team

in to three column below;

column A = 311011
column B = Team Review Panel
column C = PR Core Team

kind regards

Indra

Reply | Quote

Replies

You can use Text to Column from Data Menu

Reply | Quote

Thanks Prasad,

actually I was expecting some formula in next column, because this data will be received from various report to be consolidated in one working sheet.

using text to column is considered as second option.

regards

Indra

Reply | Quote

I have attached a solution using formulas. I’ve left the intermediate formulas in the workbook; you could incorporate them into the formulas that return the left, middle and right parts but it would make them harder to read and to edit. You could hide the columns with intermediate formulas instead.

Reply | Quote

I have attached a solution using formulas. I’ve left the intermediate formulas in the workbook; you could incorporate them into the formulas that return the left, middle and right parts but it would make them harder to read and to edit. You could hide the columns with intermediate formulas instead.

A new way to split values but somehow limited to make calculation as it will return all the values as text.

Reply | Quote

A new way to split values but somehow limited to make calculation as it will return all the values as text.

In situations like this, it is often desirable to leave the result as text. But if necessary, one could use

=VALUE(LEFT(A2,B2-1))

for the left part to return a number value.

Reply | Quote

In situations like this, it is often desirable to leave the result as text. But if necessary, one could use

=VALUE(LEFT(A2,B2-1))

for the left part to return a number value.

Got it. Thanks Hans to make me clear.

Reply | Quote

Psasad’s suggestion of using Data | Text to Columns… is very good, but I’d choose Delimited in step 1, and tick Other and specify – as delimiter in step 2. You then only need to click Finish.

Reply | Quote

Hans,

this is exactly what I need. intermediate formula is brilliant approach to teach guy like me!

many thanks,

indra

Reply | Quote

I would like to suggest for extracting Right most portion, without limiting to 100 cheractors:

use
=RIGHT(A2,LEN(A2)-C2)

insted of
=MID(A2,C2+1,100)

Reply | Quote

If the third part can be any length, it would indeed be better to use the RIGHT function instead of the MID function.

If the length of the third part is limited, MID will work fine; it is possible of cours to specify a larger or smaller number than 100.

Reply | Quote

You could also use:
=MID(A2,C2+1,len(A2))

Reply | Quote