split data to another cell

[balu7979]

I have a data in excel sheet. CUSTOMER ID : YTLPK-4635-4635-LP463501, And I want split data to another cell, the following data before semi column data is not change, after semi column data will change. I want to split the data after semi column to another cell. We are using text to column command for this, It is working but we want to use formula for this. I want to split the data using with word count. (CUSTOMER ID : ) total 14 Words includes (space & it will Never Change. Please give me the solution

This The Sample data
Never Change

CUSTOMER ID : PART ID :

DESTINATION :

LOC FIPS :

MSA ID :

PMSA ID :

DSTID :

MANUFACTURER:

BRAND NAME:

FACILITY TMQ:

<tbody>
</tbody>

 

[FormR]

Hi, it's not that clear - but maybe?

Excel 2013
	A	B	C
1	CUSTOMER ID : YTLPK-4635-4635-LP463501	CUSTOMER ID	YTLPK-4635-4635-LP463501
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(":",A1)-2)
C1	=MID(A1,FIND(":",A1)+2,255)

 

[balu7979]

Thanku very much for u r reply
Can u Explain whats is cell b1 formula -2) & c1 +2,255) in the formula

 

[FormR]

Can u Explain whats is cell b1 formula -2) & c1 +2,255) in the formula

Hi,

Find(":",A1) returns the position of the semicolon, we take 2 away because we want to return all of the text up to 2 characters before that semicolon.

Similar for the second formula, we want to start 2 characters away from the semicolon, the 255 is an arbitrary number that is assumed to be larger than the number of characters after the semicolon.

 

[hiker95]

balu7979,

How about something like this?

Excel 2007
	A	B
1	CUSTOMER ID : YTLPK-4635-4635-LP463501	YTLPK-4635-4635-LP463501
2	PART ID : B-LTh463-W/LP4635-065H	B-LTh463-W/LP4635-065H
3	DESTINATION : RIGA	RIGA
4	LOC FIPS : PLPS-4635-46LPdsLPKN4546	PLPS-4635-46LPdsLPKN4546
5	MSA ID : SAL4M-463-463-4LP46/46	SAL4M-463-463-4LP46/46
6	PMSA ID : SA73OAM73UI-7367UI-U736766	SA73OAM73UI-7367UI-U736766
7	DSTID : UIR-DST-736766-U73676	UIR-DST-736766-U73676
8	MANUFACTURER: SYNTHES BETTLACH	SYNTHES BETTLACH
9	BRAND NAME: DHS/DCS COUPLING SCREW	DHS/DCS COUPLING SCREW
10	FACILITY TMQ:DENTAL CEPHALOMETRIC	DENTAL CEPHALOMETRIC
11
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(MID(A1,FIND(": ",A1)+2,LEN(A1)),MID(A1,FIND(":",A1)+1,LEN(A1)))

The formula in cell B1, copied down:

=IFERROR(MID(A1,FIND(": ",A1)+2,LEN(A1)),MID(A1,FIND(":",A1)+1,LEN(A1)))

 

[Ingolf]

Or this formula in B1:

=TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1)))