IDEAS FOR SUBSTITUTE TEXT

[Sara_Bertomeu]

Hellous, Holaaaaaaaaaaa

I have to substitute text and I know I have to do it with SUBSTITUTE, but not found the solution. can someone from this clever forum help me?

The text is: in field "NOTAS".

Show More

Ref: 6b7e619c
ID:_6pj6aoph60sj4p1jcpgiqp9n60r3ep9o6so38dhjccqj4d1o75ij8e366dh64o9jcgr38c1o81gmisj2dph2sorfdk

NOTAS: ID:_6pj6aoph60sj4p1jcpgiqp9n60r3ep9o6so38dhjccqj4d1o75ij8e366dh64o9jcgr38c1o81gmisj2dph2sorfdk


Pax: 1
Pax: 1

I try to have only the next expresion with new values: 
Ref: 6b7e619c
ID:1as1iaqbporuhf16ivr91eslbh

NOTAS: PAGADO 70€

Pax: 1

This is because I copy several times the field "NOTAS" from others tables. 

How can I actualice the new data WITHIN erase the data after Notas. I can't find it. 

I try this, but not: (do not laugh)

IFS(

ISBLANK([NOTAS]),CONCATENATE(

IF(ISNOTBLANK([Número de reserva]),CONCATENATE("Ref: ",[Número de reserva]),""),

" ", "ID: ",[ID_ROW],"",

" "," ","Pax: ", [PAX], " ",

IF(CONTAINS([LINK],"HTTP"),CONCATENATE("LINK: ",HYPERLINK([LINK],"VER")),"") ),

ISNOTBLANK([NOTAS]),SUBSTITUTE([NOTAS],[NOTAS],

CONCATENATE( IF([Número de reserva]<> LEFT([NOTAS],25),CONCATENATE("Ref: ",[Número de reserva]),[Número de reserva])," ",

" ", "NOTAS: ",[NOTAS],

IF(CONTAINS([LINK],"HTTP"),CONCATENATE("LINK: ",HYPERLINK([LINK],"VER")),"") ) ))

Thanks Sara

[dbaum · Accepted solution]

I don't follow what you need to accomplish. Nonetheless, one potential starting point for your troubleshooting is the following portion of your expression:

SUBSTITUTE([NOTAS],[NOTAS],

That syntax means you're looking to substitute for the entire contents of the row's [NOTAS] column. If that's indeed what you want to do, you don't need the SUBSTITUTE function; instead, just include the portion of your existing expression that yields the new text:

IFS(
ISBLANK([NOTAS]), "{text to populate the blank NOTAS column}", 
ISNOTBLANK([NOTAS]), "{text to overwrite the existing text in the NOTAS column}"
)

Also, if the text to populate the column is actually the same regardless of whether the column was previously blank or previously populated, then you don't need IFS but rather just the expression necessary to provide the text you want in the column:

"{text to populate the NOTAS column}"

On the other hand, if you need to indeed only substitute a portion of the text in [NOTAS], then the second argument ("old-text" from the help article) of the SUBSTITUTE function needs to result in the text substring within [NOTAS] that needs to be replaced.

[dbaum · Accepted solution]

I don't follow what you need to accomplish. Nonetheless, one potential starting point for your troubleshooting is the following portion of your expression:

SUBSTITUTE([NOTAS],[NOTAS],

That syntax means you're looking to substitute for the entire contents of the row's [NOTAS] column. If that's indeed what you want to do, you don't need the SUBSTITUTE function; instead, just include the portion of your existing expression that yields the new text:

IFS(
ISBLANK([NOTAS]), "{text to populate the blank NOTAS column}", 
ISNOTBLANK([NOTAS]), "{text to overwrite the existing text in the NOTAS column}"
)

Also, if the text to populate the column is actually the same regardless of whether the column was previously blank or previously populated, then you don't need IFS but rather just the expression necessary to provide the text you want in the column:

"{text to populate the NOTAS column}"

On the other hand, if you need to indeed only substitute a portion of the text in [NOTAS], then the second argument ("old-text" from the help article) of the SUBSTITUTE function needs to result in the text substring within [NOTAS] that needs to be replaced.

[Sara_Bertomeu]

Gracias, esto es lo que necesito:

De hecho, solo necesito sustituir una parte del texto en [NOTAS] , luego el segundo argumento ("texto antiguo" del artículo de ayuda ) de la función SUSTITUIR debe dar como resultado la subcadena de texto dentro de [NOTAS] que debe ser sustituido.      

I can't found it. 

[dbaum · Accepted solution]

¿Cuál es el valor entero original que hay que revisar? ¿El siguiente? ¿Cuáles partes pueden variar de fila en fila? ¿Las resaltadas en rojo? ¿Se varía el largo de algunos segmentos? Por ejemplo, ¿es siempre el texto de "ID" exactamente 91 carácteres de largo?

Ref: 6b7e619c
ID:_6pj6aoph60sj4p1jcpgiqp9n60r3ep9o6so38dhjccqj4d1o75ij8e366dh64o9jcgr38c1o81gmisj2dph2sorfdk

NOTAS: ID:_6pj6aoph60sj4p1jcpgiqp9n60r3ep9o6so38dhjccqj4d1o75ij8e366dh64o9jcgr38c1o81gmisj2dph2sorfdk


Pax: 1
Pax: 1

¿Cuál el valor entero nuevo que hay que conseguir? ¿El siguiente?

Ref: 6b7e619c
ID:1as1iaqbporuhf16ivr91eslbh

NOTAS: PAGADO 70€

Pax: 1

Según los detalles, puede ser un caso bastante complicado para resolver. Quizás valga la pena utilizar las funciones de texto más flexibles de Excel/Sheets--sobre todo si es un caso de tener que limpiar la información actual de una sola vez pero no hará falta repetir el proceso continuamente.

[Sara_Bertomeu]

@dbaum thanks for your BIG patient with me. Yes I supouse. Thanks again

[Sara_Bertomeu]

Trying to find solution with test, thansk @dbaum 

maybe with SPILT.