Changing text from one column to another

I'm not sure how to formulate this so ill just paste and explain that way, i have a column that looks as follows:

Expeditionsärende telefon
Avbokat kundmöte
Expeditionsärende telefon
Bokning
Expeditionsärende telefon
Bokning
Expeditionsärende telefon
Expeditionsärende F2F
Genomfört kundmöte Webex
Avbokat kundmöte
Avbokat kundmöte
Expeditionsärende telefon
Bokning
Genomfört kundmöte telefon
Bokning
Genomfört kundmöte Webex
Avbokat kundmöte
Genomfört kundmöte Webex
Genomfört kundmöte Webex
Avbokat kundmöte
Expeditionsärende telefon
Avbokat kundmöte
Expeditionsärende telefon


What i want to do is rename all the "Expedition xxx" to only Expedition, "Genomfört xxx" to Genomfört, but everything else stays the same... any ideas? Also, i need this to be for the whole column, not a range since the data gets larger every week and i want this to happen automatically when i uppdate the data... Any ideas?

Added info: I need this to be done using a formula and i need the original data intact, so basically copying the one column to a new one with the wanted changes.

Hi

try using below formula in next column where you data is placed, i have considered the data is placed in column A cell 1, hence the formula is in form B1

=IF(ISERROR(SEARCH("Expedition",A1,1)),A1,IF(SEARCH("Expedition",A1,1)=1,CONCATENATE("Genomfort",RIGHT(A1,LEN(A1)-10)),""))

Originally Posted by amy_d2

Hi

try using below formula in next column where you data is placed, i have considered the data is placed in column A cell 1, hence the formula is in form B1

=IF(ISERROR(SEARCH("Expedition",A1,1)),A1,IF(SEARCH("Expedition",A1,1)=1,CONCATENATE("Genomfort",RIGHT(A1,LEN(A1)-10)),""))

Okay!! Great start! Still need a bit of help. Im changing all the expedition as well as all the genomfört... Now only the expedition are changing. Also i get a "#VALUE!" when it is expedition F2F, how do i adjust that?

Thanks a bunch Amy!! Ill copy paste what the result was and how i want it to look!

1.JPG

WHAT I WANT:

2.JPG

Hi alexaskmo,

use below formula to achieve result as per image 2

=IF(ISERROR(SEARCH("Expedition",A1,1)),IF(ISERROR(SEARCH("Genomfört",A1,1)),A1,"Genomfört"),"Expedition")

This is because of difference in language, Check if this works for you

=IF(ISERROR(SEARCH("Expedition";A1;1));IF(ISERROR(SEARCH("Genomfört";A1;1));A1;"Genomfört");"Expedition")

it is simple if function works on logical terms

IF(your logical test, value if true then what to do , value if false then what to do)

and to apply it to other column put $ sign to A1 whereever you find them in column Like $A$1