macro to delete unwanted characters and give output on the same cel

[sparkerk5]

hi ,

i have below data where i want to delete some unwanted characters and give output on the same cell replacing the text

Example

9 QR 730 H 08JUL 3 DFWDOH HK8 1810 1640 09JUL E QR/PV2ZPV

Output required

QR 730 08JUL DFWDOH 1810 1640 09JUL QR/PV2ZPV

pls help thx

[huuthang_bd]

What is the rule to delete characters?

[sparkerk5]

i am trying to trim the unwanted character and try to get below desired result

Output required

QR 730 08JUL DFWDOH 1810 1640 09JUL QR/PV2ZPV

[AliGW]

How do you decide what is unwanted and what should stay?

[jindon]

See the link below.

https://www.excelforum.com/excel-pro...-the-data.html

I just stop posting codes for asking one by one and might be endless...

[sparkerk5]

hi

based on the code below just a small adjustment required , it should remove the highlighted part also, kindly find the cross ref link also for reference

https://www.excelforum.com/excel-pro...ml#post5362816

J5CG4Z
BOMVS316E/2005NM/5JUL20
1.SINGH/RANGPAL MR
2 QR 146 H 10JUL 5 BCNDOH HK1 1440 2145 10JUL E QR/J5CG4Z
3 VR 053 Y 11JUL 6 DOHDEL GK1 0800 1405 11JUL SUKANYA

Please Login or Register  to view this content.

[alansidman]

We have two issues with your post.

1. Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

2. Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[sparkerk5]

thx for the update will keep that in mind next time

[alansidman]

If you expect any help on this thread, please fix your post as requested in Post #7

NO help to be provided until conditions requested in Post #7 are remedied

[sparkerk5]

hi

i have done as your request in and added # around the code which meet requirement Forum rule #2 also find find link to meet the requirement for Forum Rule #3

https://www.mrexcel.com/board/thread...-data.1139036/

hope this clears everything and i am sorry for any discomfort from my end.

Thanks

[sparkerk5]

hi

i have updated details as per your requirement, kindly provide me a solution for my post #6

Thanks

[alansidman]

you need to explain the logic for removing the parts you have indicated. Cannot see any rationale. Please expand your discussion of why and basis. Without understanding this, there is no way anyone will supply a solution.

[sparkerk5]

hi,

The eg given in post #6 gives out below output, where the highlighted parts are not required to be shown, so i require to code trim the highlighted parts also

J5CG4Z
BOMVS316E/2005NM/5JUL20
1.SINGH/RANGPAL MR
QR 146 10JUL Barcelona (BCN) Doha (DOH) HK1 1440 2145 10JUL QR/J5CG4Z
VR 053 11JUL Doha (DOH) Delhi (DEL) GK1 0800 1405 11JUL UKANYA

[alansidman]

Yes. This is the same information you provided before. What is the logic for removing the highlighted items. Explain the rationale. You have only indicated what is to be removed but not the why. You continue to not offer enough of an explanation. We need to understand the logic to write code or formula.

[AliGW]

Put simply: we know WHAT you want, but we need to know WHY you have chosen what you want. Alan is generous in saying that you do not offer enough of an explanation: the truth is that you have offered NO explanation whatsoever.

Until you are able to articulate your thought processes, nobody can write a formula or code sequence to tell Excel what to 'think' each time it encounters one of these text strings. So, what are YOUR thought processes when you select the text you want to extract?

[Rick Rothstein]

To add to what Alan and Ali have posted, perhaps the following will help you understand why they are asking the questions they are asking...

Please Note
-------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

[sparkerk5]

basically the HK1 and GK1 indicate confirmation message ,hence while giving the output i do not require those that is why im trying to remove those parts so i can give an output like below

J5CG4Z
BOMVS316E/2005NM/5JUL20
1.SINGH/RANGPAL MR
QR 146 10JUL Barcelona (BCN) Doha (DOH) 1440 2145 10JUL QR/J5CG4Z
VR 053 11JUL Doha (DOH) Delhi (DEL) 0800 1405 11JUL UKANYA

also im sharing my file with eg for the same

[AliGW]

OK - so here's what you seem to be saying: find and remove any instance of HK1 and GK1 in the text. Is that it, or is there more to it? Are there other codes that need to be removed? Will these codes always be in the same position in the text string? Will they always have a space either side of them?

[Rick Rothstein]

After you read the questions Ali has asked above (in Message #18), please read what I wrote in Message #16 and then answer Ali's questions adding anything else you think might be useful.

[sparkerk5]

yes HK1 and GK1 should be removed and their position will remain the same, i have attached my file in post #17 for the same with eg to help u work on it

[AliGW]

OK - what about HK5 and HK8? I asked if there are other codes - how many different codes? Are they always HK or GK plus one numeric, or are there other combinations? You have not answered all of my questions in post #18 yet.

The attachment does not get us any nearer to an understanding of the workings of your brain - sorry.

[sparkerk5]

HK and GK will associated with number 1 to 9 only, it will not increase for

eg it will in this range HK1 ,HK2, HK3, HK4,HK5, HK6,HK7,HK8 and HK9 but i will never exceed from 9 , same case will be in GK it will never exceed 9

[AliGW]

Questions still to be answered:

1. Will they (the codes) always have a space either side of them?
2. Is it just HK and GK, or are there other prefixes?

It has taken over ten days to drag any useful information out of you so far and we still haven't got answers to all of our questions. Please read Rick's post #16.

[sparkerk5]

1. Will they (the codes) always have a space either side of them?

yes the space will be same either side of them

2. Is it just HK and GK, or are there other prefixes?

only prefixes like HK and GK , and HL, only these will be there.

[AliGW]

only prefixes like HK and GK , and HL, only these will be there.

What does 'like' mean? We now have three ... Are there any more?

[sparkerk5]

those are the only ones which will be there nothing more

[AliGW]

Right. Here's what you should have told us ten days ago:

I want to remove any 3-character string that starts with HK, GK or HL and ends in a single digit between 1 and 9 from a much longer string. The 3-character string will always have a space either side of it.

Why was it so difficult for you to tell us this? Is my summary accurate?

[sparkerk5]

yes thats accurate ,

i was unable to understand how to frame the question, but the way you have explained it ill try to explain in the same way for future references.

[AliGW]

To be fair, you didn't even try.

Hopefully one of the VBA experts can take it from here. Thanks for confirming.

[Rick Rothstein]

See if the macro below does what you want. Note that you have to set the StartRow and DataColumn variables for the cell where your data actually starts at and set the OutputCell variable to the address fro the first cell where output should start at (I highlighted the rows where these variables must be set at in red to help you find them easier).

Please Login or Register  to view this content.

[sparkerk5]

thx for the update, but i was looking to update the same in below code,

Please Login or Register  to view this content.

[Rick Rothstein]

I do not do Regular Expression... I gave you what I think is working code without using Regular Expressions. If you feel you must use Regular Expressions, then you will have to wait for someone who knows how to program with them.

[sparkerk5]

thx for the help sir, ill wait for an update from Mr Jindon as above code was made by him.

[Haluk]

I don't know what your code is supposed to do but; while waiting Jindon, you can try the following to eliminate the strings like HK1, HL8, GK3..... from your data.

The codes uses the data as given in your sample file message #17

Please Login or Register  to view this content.

[jindon]

Unless you upload a complete set of data that shows all the possible patterns and EXACT result that you want, I will not write a code.
It will be just wasting time.

[zzzeddy]

Hi sparkerk5

..i want to delete some unwanted characters and give output on the same cell replacing the text

I've looked through this thread and it seems pretty simple to remove unwanted characters and leave the updated text in the same cell.
So, if your original text is in column A, this easy-to-understand macro will do what you have asked..

Please Login or Register  to view this content.

zeddy

[sparkerk5]

hi jindon ,

the code which you had made earlier is fine an it give out the desired result which i want, howvever its is only missing out to erase the "HK1" part in that line.

I am attached the workbook so can have look at the

Sample data :

QGYSZI
BOMVS316E/2005NM/11JUL20
1.ALAM/MOHD MAHFOOZ MR
2 QR 729 K 19JUL 7*DOHDFW HK1 0750 1530 19JUL E QR/QGYSZI
3 QR7584 K 19JUL 7*DFWMOB HK1 2105 2246 19JUL E QR/QGYSZI


Output given by your code :

QGYSZI
BOMVS316E/2005NM/11JUL20
1.ALAM/MOHD MAHFOOZ MR
QR 729 19JUL Doha (DOH) Dallas (DFW) HK1 0750 1530 19JUL QR/QGYSZI
QR 7584 19JUL Dallas (DFW) Mobile (MOB) HK1 2105 2246 19JUL QR/QGYSZI


Actual Output required

QGYSZI
BOMVS316E/2005NM/11JUL20
1.ALAM/MOHD MAHFOOZ MR
QR 729 19JUL Doha (DOH) Dallas (DFW) 0750 1530 19JUL QR/QGYSZI
QR 7584 19JUL Dallas (DFW) Mobile (MOB) 2105 2246 19JUL QR/QGYSZI


Please find the code earlier made by you

Please Login or Register  to view this content.

Hope this helps.

Thanks

[zzzeddy]

Hi sparkerk5

So, you didn't like my simpler code then???

zeddy

[zzzeddy]

Hi sparkerk5

Here's my file with your sample data.


zeddy

[zzzeddy]

Hi sparkerk5

I can make it more complicated if you want.
But it's not rocket science.

zeddy

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[sparkerk5]

hi,

ur revised code given code is working fine, except its is repeating the arrival city(highlighted) part again instead of replacing it .

RXS6U7
BOMVS316E/1117RP/15JUL20
1.MUTHU/ESWARAMOHAN MR 2.PRASADE/SAGAR ARVIND MR
3.SHIRSAT/VINAYAK VILAS MR 4.SIHAG/SUNIL KUMAR MR
5.SINGH/SATISH MR
UA 802 22JUL Delhi (DEL) Newark (EWR) EWR 2335 0455 23JUL UA/L3NMYD
UA 1235 23JUL Newark (EWR) Houston (IAH) IAH 1015 1252 23JUL UA/L3NMYD

[jindon]

This is the last.
Just upload a workbook with the data and EXACT result that you want without password protect vbe.

Why should I work with vbe protected workbook?

[sparkerk5]

hi

jindon sorry for the same, kindly find the file attached i have removed the password protection

Thanks

[jindon]

change to

Please Login or Register  to view this content.

[sparkerk5]

thx that worked fine.

Appreciate your help.