Remove characters before and after

[sholleman]

[TABLE="width: 256"]
<tbody>[TR]
[TD="colspan: 4"]This is what i have: (The 0 and CA are on one line- 4 lines total)

000433821517 00114427
0 CA[/TD]
[/TR]
[TR]
[TD="colspan: 4"]000645000670 00234411 0 CA[/TD]
[/TR]
[TR]
[TD="colspan: 4"]000790810056 00229763 0 CA[/TD]
[/TR]
[TR]
[TD="colspan: 4"]000901000017 00068794 0 CA[/TD]
[/TR]
</tbody>[/TABLE]

This is what I want (keep second column, remove front zeros, add k to front)
[TABLE="width: 256"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 4"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]k114427[/TD]
[/TR]
[TR]
[TD]k234411[/TD]
[/TR]
[TR]
[TD]k229763[/TD]
[/TR]
[TR]
[TD]k68794

[TABLE="width: 256"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 4"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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