change batch of series name in cloum

[nima94]

hi

i have a column which includes of series of voucher code as bellow:

cash voucher #2011

cash voucher #2012

cash voucher #2036

cash voucher #3020

cash voucher #3036

and many for #50** #60**

and also welcome25 and many other voucher name

now i want to transfer all of those cash series which starts with : cash voucher #20** to: cash#20 all 30** series to : cash#30 and same for the rest and still show other voucher name as well in the column such as welcom25.

can one one help me please ?

pleases find the attachment.

[kaushiknsolanki]

Hi Can you share some sample data with all scenarios you want to capture.

[nima94]

Hi thanks for your replay 

please find the attached image.

[Kushal_Chawda · Accepted solution]

@nima94  Assuming that the format of your voucher values are exactly same as attached image then you can try below. It will work for all other like #60 .. 

Data:
LOAD *,
     if(index(lower(trim(Voucher)),'cash voucher #cv'),
     'CASH '&right(mid(Voucher,index(Voucher,'#')+1,4),2),
     Voucher) as Voucher_New
 Inline [
Voucher
CASH VOUCHER #CV2030
CASH VOUCHER #CV2061
CASH VOUCHER #CV20**
CASH VOUCHER #CV3030
CASH VOUCHER #CV3061
CASH VOUCHER #CV30**
CASH VOUCHER #CV5030
CASH VOUCHER #CV5061
WELCOME 25
SUMMER VOUCHER
EOM VOUCHER ];

[nima94]

Many Thanks.

i did till

Data:
LOAD *,
     if(index(lower(trim(Voucher)),'cash voucher #cv'),
     'CASH '&right(mid(Voucher,index(Voucher,'#')+1,4),2),
     Voucher) as Voucher_New

Here

And It works.