Find and replace a character to create a new cell

For example if have

" size:1, color:red CH 4开发者_运维百科"

in one cell, I want

" size:1. color:red" new cell "4"

Basically, find "CH" and replace it with a new cell and everything in front of it. Anyone know how?

Yeap, I do this stuff all the time. You'll need two functions, they're found under the text section. THey're called SEARCH & MID.

What you are going to want to do is use an excel function SEARCH to first find the position of "CH" - this will be different depending on the length of the other strings.

Then you use another excel function, MID, to extract a substring starting from the location you found in the first function. Now, you can copy the column that just has "4" in it and use "Paste Special" from the right click menu. In paste special, choose "values". This will allow you to move the actual value the function generated and not the function itself.

Detailed Example

A1                            B1                        C1
size:1, color:red CH 4        =SEARCH("CH",A1)+3        =MID(A1,B1,1)
(Text we want to decode)      (Returns 21)              (returns 4)

+3 is because CH starts at the 19th character, then there is the "H" and a space, making the number your 3rd digit (+3). Then do the "Paste Special" procedure on cell C1, and you have isolated that value.