Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo encontrar y reemplazar múltiples valores a la vez en Excel?

doc multiple find reemplaza 1

Como todos sabemos, podemos usar la función Buscar y Reemplazar para encontrar varias mismas células y reemplazarlas por un valor según lo necesite. Pero a veces, debe aplicar reemplazo de muchos a muchos simultáneamente. Por ejemplo, tengo un rango de datos, y ahora quiero reemplazar todas las manzanas por manzanas rojas, naranjas por naranjas verdes, plátanos por bananas amarillas, etc., como se muestra en las siguientes capturas de pantalla, ¿tiene alguna buena idea para resolver esta tarea en ¿Sobresalir?

Encuentra y reemplaza varios valores a la vez con el código VBA


Busque y reemplace en libros abiertos o en varias hojas de trabajo:

Kutools for Excel, Buscar y reemplazar la característica puede ayudarlo a encontrar y reemplazar los valores de los libros abiertos o las hojas de trabajo específicas que necesita.

doc-multiple-find-and-replace-6

flecha azul burbuja derecha Encuentra y reemplaza varios valores a la vez con el código VBA


Si está cansado de encontrar y reemplazar los valores una y otra vez, el siguiente código VBA puede ayudarlo a reemplazar múltiples valores con sus textos necesarios a la vez.

1. Cree las condiciones que desee usar que contengan los valores originales y los nuevos valores. Ver captura de pantalla:

doc multiple find reemplaza 2

2. A continuación, mantenga presionada la tecla ALT + F11 teclas para abrir el Microsoft Visual Basic para la ventana de Aplicaciones.

3. Hacer clic recuadro > Móduloy pegue el siguiente código en la ventana del Módulo.

Código VBA: Encuentre y reemplace valores múltiples a la vez

Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

4. Entonces presione F5 clave para ejecutar este código, en el cuadro emergente, especifique el rango de datos en el que desea que se reemplacen los valores con nuevos valores.

doc multiple find reemplaza 3

5. Hacer clic OK, y se muestra otro cuadro de aviso para recordarle que seleccione los criterios que se crearon en el paso 1. Ver captura de pantalla:

doc multiple find reemplaza 4

6. Luego haga clic OK, todos los valores específicos han sido reemplazados por los nuevos valores según lo necesite de inmediato.

doc multiple find reemplaza 5


Artículos relacionados:

¿Cómo encontrar y reemplazar texto específico en cuadros de texto?

¿Cómo encontrar y reemplazar texto en títulos de gráficos en Excel?

¿Cómo encontrar y reemplazar texto en comentarios en Excel?

¿Cómo cambiar varias rutas de hipervínculo a la vez en Excel?


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tanay · 16 days ago
    Hello. Thank you for the help. But it is not renaming the entire cell if it is a large string. For any cell which has more than 21 letters, it can only replace 21 letters and then else is same. Please help.
  • To post as a guest, your comment is unpublished.
    MURUGAN T G · 1 months ago
    Hi im TG,Thank you for posting this page, It's very useful and make it very simple of my work and save more time , thank you sir....
  • To post as a guest, your comment is unpublished.
    Jairo L. · 3 months ago
    Muchas Gracias me fue de mucha utilidad el codigo.
  • To post as a guest, your comment is unpublished.
    iris · 4 months ago
    it works! THANK YOU SO MUCH!
  • To post as a guest, your comment is unpublished.
    Jon · 7 months ago
    Макрос не работает корректно !!! Пытался подобным образом заменить символы, но не различаются маленькие и большие буквы.
  • To post as a guest, your comment is unpublished.
    Ugnė Vitkutė · 8 months ago
    Hi, I would like to replace whole cells in the entire Excel file (with many sheets). What should be replace in the Original Range to do that? Thanks.
  • To post as a guest, your comment is unpublished.
    Ugne · 8 months ago
    What should be replaced in the code to run it on all the Excel file's sheets?
  • To post as a guest, your comment is unpublished.
    Tina Sun · 9 months ago
    Can I ask if I want to replace the entire cell based on partial match, how should I alter the code?
    For example: I want to code all cells containing the word apple to 1 (regardless whether it's "green apple" or "red apple"), I want all of them to turn into 1.
  • To post as a guest, your comment is unpublished.
    Carlos · 10 months ago
    El mejor!!! Mil gracias por compartir estos conocimientos, fue increíble la ayuda que me proporciono este programa en VBasic (nunca lo había usado hasta ahora), lo pensaba realizar en php o similar pero leyendo encontre esta valiosa información.

    Gracias!
  • To post as a guest, your comment is unpublished.
    Abhishek · 11 months ago
    The VBA method of find and replace is not working. It was working for me till yesterday but today I am not able to do any find and replace using it today. Can you guys please help
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hi, Abhishek,
      After inserting the code into the workbook, you should save the workbook as Excel Macro-Enabled Workbook format to keep the code without losing it.
      Please try it, hope this can help you!
  • To post as a guest, your comment is unpublished.
    Am · 1 years ago
    This is causing my original values data to also be replaced, in addition to the column of data I want to be replaced. How do I stop this?
  • To post as a guest, your comment is unpublished.
    Joe · 1 years ago
    Thanks, this worked perfectly.
  • To post as a guest, your comment is unpublished.
    mohan · 1 years ago
    Hi

    I have to replace list of bank with proper name based on key words. For example, if somebody enters Halifax, it should be Halifax Bank of Scotland (HBOS) or if somebody enters LloydsTSB, it should be Lloyds.

    Original value Replacing Value
    LloydsTSB : Lloyds
    Santander : Santander (Abbey)
    Argos Card Services : Argos
    Halifax : Halifax Bank of Scotland (HBOS)

    The down formula works in many cases. But if there is two Halifax bank on my list, I will get Halifax Bank of Scotland (HBOS) (HBOS)
    . So, first time it finds Halifax, replace with correct one. Again it finds Halifax and replace. How can I correct this problem ?


    Sub FindReplaceNew()
    Dim xTitledId As String
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitledId = "Test"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Original Range", xTitledId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Replace Range :", xTitledId, Type:=8)
    Application.ScreenUpdating = False

    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
    Next
    Application.ScreenUpdating = False


    End Sub
  • To post as a guest, your comment is unpublished.
    David Gaertner · 1 years ago
    I modified this to work on Microsoft Excel for Mac 2016 and to replace strings that are within whole words. Here are the first and last lines I changed (along with the stuff in between that I didn't change).
    Set InputRng = Application.InputBox(Prompt:="Original Range :", Title:="Range to search", Default:=InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox(Prompt:="Replace Range :", Title:="Replace mapping range", Type:=8)
    Application.ScreenUpdating = False
    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlPart
  • To post as a guest, your comment is unpublished.
    Etienne · 1 years ago
    Hello, how can I only replace full words (and not strings within a word) ? By word, I mean a string that is at the beginning/end of the cell or preceded/followed with a space.
    • To post as a guest, your comment is unpublished.
      Khamir · 1 years ago
      Use this code.


      Sub MultiFindNReplace()
      Dim Rng As Range
      Dim InputRng As Range, ReplaceRng As Range
      xTitleId = "MultipleReplaceValue"
      Set InputRng = Application.Selection
      Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
      Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
      Application.ScreenUpdating = False
      For Each Rng In ReplaceRng.Columns(1).Cells
      InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlWhole, MatchCase:=True
      Next
      Application.ScreenUpdating = True
      End Sub
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Etienne,
      Can you give an example screenshot for your problem?
      Thank you!
  • To post as a guest, your comment is unpublished.
    Etienne · 1 years ago
    Hello, how can I replace only full words. Replace only if the string is at the beginning/end of the cell or preceded/followed by a space ?
  • To post as a guest, your comment is unpublished.
    pratik · 1 years ago
    Sub MultiFindNReplace()

    'Update 20140722

    Dim Rng As Range

    Dim InputRng As Range, ReplaceRng As Range

    xTitleId = "KutoolsforExcel"

    Set InputRng = Application.Selection

    Set InputRng = Application.InputBox("Original Range
    ", xTitleId, InputRng.Address, Type:=8)

    Set ReplaceRng = Application.InputBox("Replace Range
    :", xTitleId, Type:=8)

    Application.ScreenUpdating = False

    For Each Rng In ReplaceRng.Columns(1).Cells

    ' InputRng.Replace what:=Rng.Value,
    replacement:=Rng.Offset(0, 1).Value

    InputRng.Replace
    Rng.Value, Rng.Offset(0, 1).Value

    Next

    MsgBox "Done Successfully....", vbInformation

    Application.ScreenUpdating = True

    End Sub
  • To post as a guest, your comment is unpublished.
    ramanuj sharma · 1 years ago
    THANKS A LOT.....
    ITS MOST EFFECTIVE CODE TO USE....


    THANKS SIR,
  • To post as a guest, your comment is unpublished.
    francesco · 1 years ago
    how do I implement "match entire cell content" in this code?
    • To post as a guest, your comment is unpublished.
      Boris Chevreau · 1 years ago
      in case someone else is looking for this, here it is:

      Replace this line
      InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value

      by
      InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlWhole



      It will look for the whole cell in target and original, and replace by a whole cell instead of particular instances of doubles inside a cell
  • To post as a guest, your comment is unpublished.
    Jorge Vargas · 1 years ago
    don't work in all the columns for me idk why
  • To post as a guest, your comment is unpublished.
    Hans Filbert · 1 years ago
    Perfect Solution.
    Easy to use.
  • To post as a guest, your comment is unpublished.
    Kalle · 1 years ago
    Hi, this was very powerful and useful code. Thanks a lot! There is just a couple of features lacking and it would be perfect for me: 1) Could it be possible to modify the code so that it would process multiple files at once? For example you have your find_replace attributes in different file and then you just name the files in the VBA code you want to modify. I dunno...That would be cool. 2) Could it be possible to make a report so that I would know what attributes were replaced. So that I could double-check if something went wrong.
  • To post as a guest, your comment is unpublished.
    Roshan · 1 years ago
    this is very useful .. Thanks You!!
  • To post as a guest, your comment is unpublished.
    dSd · 1 years ago
    Excellent work!!!!!
    This saved me after my enterprise free period got over!
  • To post as a guest, your comment is unpublished.
    karel · 1 years ago
    wohoo, made my day. thank you
  • To post as a guest, your comment is unpublished.
    styleguerilla · 2 years ago
    How can I make this VBA macro work for a formula, e.g. if I want to replace 'Sheet1!$A:$A' with Table1[A]?
  • To post as a guest, your comment is unpublished.
    Jason Ebensberger · 2 years ago
    This is AWESOME!!! Very simple solution to my problem. THANK YOU!
  • To post as a guest, your comment is unpublished.
    sohberson · 2 years ago
    in case anybody still struggles with replacing entire cell value and not just part: use Lookat:=xlWhole

    Sub MultiFindNReplace()
    'Update 20140722
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value , replacement:=Rn g.Offset(0, 1).Value, Lookat:=xlWhole
    Next
    Application.ScreenUpdating = True
    End Sub
    • To post as a guest, your comment is unpublished.
      JProf · 1 years ago
      Was looking for this specifically - thank you!
  • To post as a guest, your comment is unpublished.
    Nathan Rona · 2 years ago
    thanks, you're great. Your script saved me at least half an hour of find and replace :lol:
  • To post as a guest, your comment is unpublished.
    Avril · 2 years ago
    This tutorial was excellent! Did what I needed it to do.

    Is there a way to automatically run this macro on specific columns every time I open it/after pressing a key or something easy? After I've already saved the macro on a macro enabled file. I am creating a document to share with my colleagues and I'm not sure everyone will follow all the steps.

    Thank you!
  • To post as a guest, your comment is unpublished.
    Jin · 2 years ago
    xxx 1
    xxx 2
    xxx 3
    xxx 4
    xxx 5
    xxx 6

    Sir, how can i replace 1, 2, 3, 4, 5, 6 into xxx in a very short period of time?
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    This VBA script just saved me hours if not days of find and replace data entry. Thanks for sharing :)
  • To post as a guest, your comment is unpublished.
    Christopher Witmer · 2 years ago
    This saved me hours of work. Thank you soooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo much.
  • To post as a guest, your comment is unpublished.
    Jeremiah H · 2 years ago
    I LOVE this formula and use it all the time!

    I need a very similar formula but can't work out the logic.

    Column A is full cells with lots of text.

    Column B is full of my 'keywords' that I want to extract, much like the 'Original Value' in this formula.

    Column C needs to be the words from Column A that match the 'keywords' in Column B. Ideally they would be separated by commas with NO spaces, but I can use tools to insert these.

    Thanks, guys, your stuff is amazing!
  • To post as a guest, your comment is unpublished.
    Siddharth Gadia · 2 years ago
    How to do it for Exact Cell match?? if not exact, it should leave it.
  • To post as a guest, your comment is unpublished.
    Michael · 2 years ago
    Hie guys, I have used the Macro Below and it worked perfectly, however I am now having challenges with filtered values, is it possible to make the effect of the macro only on the filtered data.

    Sub MultiFindNReplace()
    'Update 20140722
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
    Next
    Application.ScreenUpdating = True
    End Sub

    Please help urgently, my boss is on my neck.
  • To post as a guest, your comment is unpublished.
    Patrick Miller · 2 years ago
    So I'm trying to do a replacement range as seen below.

    Original Value Replacing range.
    0 to 1 1
    0 to 2 1,2
    0 to 99 1,2,3,4,5,6,7,8,9,10,11,12,13
    13 to 99 13

    However anything with 99 in it gives me a funky number and doesn't replace properly. I don't know what to do. Something broke as of recently Because this script was working fine last week. Now it's all broken. I'm getting replacement like 14,5,6,7,8,9,109, for the value 13 to 99. And for the value 8 to 99 it's replacing it with 19109. I'm at a complete loss here. Help!
  • To post as a guest, your comment is unpublished.
    Patrick Miller · 2 years ago
    So I'm trying to use this script to a replacement with age ranges. To a code listing. But it's not inputting correctly.

    Original Value Replacing Value
    0 to 1 1
    0 to 2 1,2
    0 to 3 1,2,3
    0 to 4 1,2,3,4
    0 to 5 1,2,3,4,5
    0 to 6 1,2,3,4,5,6
    0 to 7 1,2,3,4,5,6,7
    0 to 8 1,2,3,4,5,6,7,8
    0 to 9 1,2,3,4,5,6,7,8,9
    0 to 10 1,2,3,4,5,6,7,8,9,10
    0 to 11 1,2,3,4,5,6,7,8,9,10,11
    0 to 12 1,2,3,4,5,6,7,8,9,10,11,12
    0 to 13 1,2,3,4,5,6,7,8,9,10,11,12,13
    13 to 99 13

    As you see above this is how the replacing ranges should look. But for numbers like 13 to 99 it gets replaced with like 14,15,16,109 instead of of having the replacement be 13. I don't know what is causing this. Last week this script was working fine but now it broke.
  • To post as a guest, your comment is unpublished.
    Nirosni Srikantharaj · 2 years ago
    Hi:

    I need to replace a formula (=F108+Start!I108) with (=F108+'Jan 17'!I108) throughout the sheet. I am not able to just find and replace since there is only one cell with that formula. Each cell has its own number... Can you help to to find the replace the Start with Jan 17. Thank you.
  • To post as a guest, your comment is unpublished.
    Qudsia · 2 years ago
    This is very useful article, its works perfect for me. Thanks a lot
  • To post as a guest, your comment is unpublished.
    anubhav mehra · 2 years ago
    how to change 32642/319/09-07-08 only change 32642-319/09-07-08 this format all sheet only change first / to - not change all
  • To post as a guest, your comment is unpublished.
    Chris · 2 years ago
    Your module worked perfectly and has saved me many hours ( Thank You )

    Is it possible to find and ''highlight'' fill red for example multiple values at once in Excel
  • To post as a guest, your comment is unpublished.
    Mohanraj · 2 years ago
    Thanks a lot, this macro really helped me to complete the work in lightning speed.
  • To post as a guest, your comment is unpublished.
    Rhonda · 2 years ago
    I am using your code mostly successfully, except I need to match case. My value table is a list of Acronyms, and the Replacement Range is a list of Item Descriptions. I am replacing all acronyms in the items descriptions with the match text; ie, HDD = High Definition Digital, or DVD = Digital Video Disc. But when the code attempts to replace PROX with Proximity, and there's already the word Proximity in the item description, I get Proximityimity. How do I match the CASE?
  • To post as a guest, your comment is unpublished.
    Jaan · 2 years ago
    Possible to include count of replacements & msgbox at end showing no. of replacements made?
    Any help greatly appreciated!
  • To post as a guest, your comment is unpublished.
    kumar krishnan · 2 years ago
    I have excel sheet which have "n" number of dates in mm/dd/yyyy format.
    1. I have to count no of rows.
    2. In the range of 20 rows, need to change find and replace the date.'
    3. Replace date should also in sequence

    date
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    08/23/2016
    Replaced date
    08/25/2016
    08/25/2016
    08/26/2016
    08/26/2016
    08/29/2016
  • To post as a guest, your comment is unpublished.
    JINDE · 2 years ago
    This worked fine for me - with an exception: some of the corrected text has following asterisks (**) which are not in either of the Find and Replace columns.
    What's happening here and how can it be fixed?
  • To post as a guest, your comment is unpublished.
    Sourav · 3 years ago
    Thanks a lot.. worked like a charm..
  • To post as a guest, your comment is unpublished.
    Mena · 3 years ago
    Hi,

    I found the Kutools for Excel to be very good companions in everyday product sheet file management.

    However, something with this MultiFindNReplace VBA code seems to be odd.

    It works well when used on textual cell blocks.

    But as soon I'm trying to use it on cells containing decimals, example: 10,5 1,5 3,2 etc - it returns strange numbers as a result.

    Maybe it has something to do with Excel version, I'm suing the latest update - OR- it has to do something with (wrong) formatted cells - OR - I'm doing something wrong.

    I would really appreciate if the author could take a look.

    PS. I do plan to invest money in full version of Kutools :)

    Cheers.

    Mena
  • To post as a guest, your comment is unpublished.
    Moe · 3 years ago
    Hi guys;

    I have a dataset of values that I need to convert. There are 141 values that need converting.

    eg, 0.6
    0.6
    0.2
    0.4
    0
    that need to be converted to :
    0.4
    0.4
    0.8
    0.6
    1
    So I need to convert 0 = 1, 0.2 = 0.8, 0.4 = 0.6, 0.2 = 0.8 and 0 = 1
    Please help!

    Thank you
    Moe