Remove characters before and after

[sholleman]

This is what i have: (The 0 and CA are on one line- 4 lines total) 000433821517 00114427 0 CA

000645000670 00234411 0 CA

000790810056 00229763 0 CA

000901000017 00068794 0 CA

<tbody>
</tbody>

This is what I want (keep second column, remove front zeros, add k to front)

<tbody>
</tbody>

k114427

k234411

k229763

k68794 <tbody> </tbody>

<tbody>
</tbody>

 

[Stiuart_W]

Duplicate

Please put this in your original thread. As it is this thread is meaningless

 

[baitmaster]

with one of these text strings in A1, use ="k"&INT(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))))

 

[sholleman]

with one of these text strings in A1, use ="k"&INT(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))))

The above didn't work. Here it is again:


000433821494 00114424 0 CA
000433821500 00114425 0 CA
000433821517 00114427 0 CA
000645000670 00234411 0 CA

 

[Jonmo1]

Looks like the format of the data is pretty consistent in length.

Try
="k"&MID(A1,16,6)

 

[sholleman]

Looks like the format of the data is pretty consistent in length.

Try
="k"&MID(A1,16,6)

From the above, I got k 0011 but I want k114423.

Can you send me another formula to use.

 

[Rick Rothstein]

Does this formula do what you want...

="k"&0+MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))

 

[Jonmo1]

From the above, I got k 0011 but I want k114423.

Can you send me another formula to use.

Works for me

Excel Workbook
	A	B	C
1	000433821517 00114427 0 CA		k114427
2	000645000670 00234411 0 CA		k234411
3	000790810056 00229763 0 CA		k229763
4	000901000017 00068794 0 CA		k068794
Sheet1

 

[Jonmo1]

From the above, I got k 0011 but I want k114423.

If that's what you got from that formula, it looks like there are several spaces in there
000433821517______00114427

The _ represents a space, because the forum software will remove consecutive spaces.

Try
="k"&MID(TRIM(A1),16,6)

 

[Jonmo1]

I also notice that the 4th value you listed had a leading 0 in the resulting value that needs to be removed.

Try
="k"&MID(TRIM(A1),16,6)+0