如何替换Excel单元格的中间部分? [英] How to replace the middle of an Excel cell?

问题描述

我在Excel中有一些产品的库存清单.我需要用某些数据替换单元格的中间.

I have a list of inventory for some of my products in Excel. I need to replace the middle of the cell with certain data.

让我们说每个单元格都具有以下数据:

Let's say each cell has data like:

Ronald RFD2322 Green 34 x 55 Large

但是每个单元格都不同.

But each cell is different.

其中一个可能与上面类似，而下面的可能是

One may be like the above, and the one below may be

Ronald RFD8827 Blue 21 x 25 Small

我需要用一个名称替换中间部分，该部分看起来像型号(RFD2322)，但有些名称有所不同.我需要单元格看起来像

I need to replace the middle section, that looks like a model number (RFD2322), with a name, but some names are different. I need the cells to look like

Ronald Jimmy Green 34 x 55 Large
Ronald Sarah Blue 21 x 25 Small

如果没有答案，那么如何至少用空格替换单元格的中间部分，以便我可以回过头来手动修复它?

If no one has an answer for that, how to at least replace the middle of the cell with a blank space so I can go back and fix it manually?

推荐答案

此公式将返回 Ronald Green 34 x 55 Large (假定数据位于单元格 A1

This formula will return the Ronald Green 34 x 55 Large assuming data is in cell A1

=LEFT(a1,FIND(" ",a1)-1)&MID(a1,FIND(" ",a1,FIND(" ",a1)+1),99)

看看您的问题，如果要替换的数据位于一列中，则会更有意义.例如.如果 Jimmy 在 B1

Looking at your problem, it would more sense if the data to be substituted was in a column. E.g. if Jimmy is in B1

=LEFT(a1,FIND(" ",a1)-1)&B1&MID(a1,FIND(" ",a1,FIND(" ",a1)+1),99)

NB 99是一个随机数，选择该数字是因为不要期望您会有太多的字符.这很愚蠢，因为它对于 LEFT 和 RIGHT 函数是可选的，但对于 MID 来说是必需的.

NB 99 is a random number, chosen as don't expect you'll have more than that many characters. It's silly as it's optional for LEFT and RIGHT functions but required for MID.

这篇关于如何替换Excel单元格的中间部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！