create one space amongs digits,letters and symbols

[abdo meghari]

hello
I need making macro to make one space among numbers , letters and symbols let's take letters BS (always I have two letters in the beginning). it should make one space after two letters(BS ,FS,TQ.....)
about two numbers and letters R always I have two numbers and followed by letter R should not be space between each other of them like this (1200R20)
as to any letters contain (PR) and precede always two numbers should not be space between each other of them like this (18PR) and should be space where precede it (1200R20) and make one space after two numbers and letter R (G580) and make one space before the three letters(JAP,THI ,IND)
so based on item 2 should be like this BS 1200R20 18PR G580 JAP
as to items contain letter C the space should be after this letter and if there is no two numbers and letter R always like 18pr then should move to the next letter or number like BS 195R14C 613 JAP
as to stars BS 2400R25** VRLS JAP should make one space after stars
last thing if there are items contain hyphen"-" then should be delete it. the result in column B. when you see *** this is the short japan contains three letters J,A,P. the problem form tool XL2BB
I know there are many cases . this make things are complicated . if this is not clear just inform me

Bridgestone Sales Report 11 Nov 2021.xlsx
	A	B
1	SIZE	RESULT
2	BS1200 R20 18PR G580JAP	BS 1200R20 18PR G580 JAP
3	BS 1200 R20 18PR G580 THI	BS 1200R20 18PR G580 THI
4	BS 1200 R20 18PRM802 THI	BS 1200R20 18P RM802 THI
5	BS 1200 R24 G582 JAP	BS 1200R24 G582 JAP
6	BS13 R22.5 R187JAP	BS 13R22.5 R187 JAP
7	BS 1400R20VSJ TCF JAP	BS 1400R20 VSJ TCF JAP
8	BS 155R12C R623 IND	BS 155R12C R623 IND
9	BS 175/70R14 EP150 THI	BS 175/70R14 EP150 THI
10	BS 175/70R14 MY02 THI	BS 175/70R14 MY02 THI
11	BS 185/65R14 EP150 IND	BS 185/65R14 EP150 IND
12	BS 185/65R14 TEC THI	BS 185/65R14 TEC THI
13	BS 185/65R15 T005 IND	BS 185/65R15 T005 IND
14	FS 195/60 R15 EP150THI	FS 195/60R15 EP150 THI
15	BS 195/65R15 EP15 JAP	BS 195/65R15 EP15 JAP
16	BS 195R14C 613 JAP	BS 195R14C 613 JAP
17	BS 195R15C R623 THI	BS 195R15C R623 THI
18	TQ 205/60R16 T001 JAP	TQ 205/60R16 T001 JAP
19	BS 205/70R15C R623 THI	BS 205/70R15C R623 THI
20	BS 215/55 R17 MY-02 THI	BS 215/55R17 MY-02 THI
21	LG 215/70R15C R623 THI	LG 215/70R15C R623 THI
22	BS 225/70R15C R623 JAP	BS 225/70R15C R623 JAP
23	BS225/70R16 H005 THI	BS 225/70R16 H005 THI
24	BS 235/60R16 T01 JAP	BS 235/60R16 T01 JAP
25	BS 245/45R17 T05A JAP	BS 245/45R17 T05A JAP
26	BS 255/70R15C D840 THI	BS 255/70R15C D840 THI
27	BS 265/65R17 D840 JAP	BS 265/65R17 D840 JAP
28	BS 275/65R18 AL01 JAP	BS 275/65R18 AL01 JAP
29	BS 285/60R18 DHPS JAP	BS 285/60R18 DHPS JAP
30	BS 285/65R17 R683 JAP	BS 285/65R17 R683 JAP
31	BS 315/80R22.5 R152 JAP	BS 315/80R22.5 R152 JAP
32	BS 315/80R22.5 R184 JAP	BS 315/80R22.5 R184 JAP
33	BS 315/80R22.5 R184 THI	BS 315/80R22.5 R184 THI
34	BS 315/80R22.5-18PR G580 JAP	BS 315/80R22.5 18PR G580 JAP
35	BS 315/80R22.5-18PR G582 THI	BS 315/80R22.5 18PR G582 THI
36	BS 385/65 R22.5 R164 JAP	BS 385/65R22.5 R164 JAP
37	BS 385/65 R22.5 R164 THI	BS 385/65R22.5 R164 THI
38	BS 445/65R22.5 R164 JAP	BS 445/65R22.5 R164 JAP
39	BS 750R16R230 TCF JAP	BS 750R16R230 TCF JAP
40	BS 750R16 VSJ TCF JAP	BS 750R16 VSJ TCF JAP
41	DT 1200R24 DA53 THI	DT 1200R24 DA53 THI
42	DT 315/80R22.5 DS50 THI	DT 315/80R22.5 DS50 THI
43	DT 385/65R22.5 DT40 THI	DT 385/65R22.5 DT40 THI
44	BS 2400R25**VRLS JAP	BS 2400R25** VRLS JAP
45	BS29.5R25** VKTIND	BS 29.5R25** VKT IND
46	BS1600-2528PR RLS IND	BS 1600-25 28PR RLS IND
Sheet1

 

[PeteWright]

I need making macro to make one space among numbers , letters and symbols let's take letters BS (always I have two letters in the beginning). it should make one space after two letters(BS ,FS,TQ.....)
about two numbers and letters R always I have two numbers and followed by letter R should not be space between each other of them like this (1200R20)
as to any letters contain (PR) and precede always two numbers should not be space between each other of them like this (18PR) and should be space where precede it (1200R20) and make one space after two numbers and letter R (G580) and make one space before the three letters(***,THI ,IND)
so based on item 2 should be like this BS 1200R20 18PR G580 ***
as to items contain letter C the space should be after this letter and if there is no two numbers and letter R always like 18pr then should move to the next letter or number like BS 195R14C 613 ***
as to stars BS 2400R25** VRLS *** should make one space after stars
last thing if there are items contain hyphen"-" then should be delete it. the result in column B. when you see *** this is the short japan contains three letters J,A,P. the problem form tool XL2BB
I know there are many cases . this make things are complicated . if this is not clear just inform me

Hi @abdo meghari this could possibly be the right job for Regular Expressions if there is any kind of "logic" behind your text data.

Let's say your data can be expressed as follows:
<(exactly) two letters><one or zero space><at least two, but no more than three digits><one or zero slash signs><two or more digits><letter "R"><two digits><one or zero space><single letter><three digits><one or zero space><three letters>

This could make up following Regular Expression:
[A-Za-z]{2} ?/d{2,3}//?/d{2,}R/d/d ?[A-Za-z]/d/d/d ?[A-Za-z]{3}

If your data has this or any other kind of logic, it can be easily processed with VBA and Regular Expressions.

But, before I start over to write some code I'll need a little more information about your data.
So, could you express your data in the way I mentioned above?

 

[abdo meghari]

thanks for guide me the right way to express that ,but I thought my picture is clear . actually your way as programmer is very difficult for me.
but I do my best . I write my requirements based on your way . if you need more just tell me .

< two letters><one space><at least from three or more digits (without slash and symbols ) & letter(R)>< three or more(digits,letters)><one space>three or more letters> or < two letters><one space>< three digits or more & slash & two or more digits & letter(R) & two or more digits or letters><one space>< three or more(digits,letters)<one space> <three digits or more >or< two letters><one space><at least from three or more digits & two or more symbols & letter(R) & two or more digits or letters ><one space>< three or more(digits,letters)>< one space><three letters>
I hope this help

 

[Peter_SSs]

In your sample results ..
- In cell B4, why is there a space between the 'P' and the 'R' when there isn't in B2 and B3?
- In cell B41, why is there three spaces before the DA53?
- In cells B20 and B46, why are there hyphens when you said they should be deleted?

Also, tell us about the two ** characters in rows 44 and 45

 

[abdo meghari]

@Peter_SSs I admit to issue this thread badly way . there are many mistakes and unclear
my apologies ?

- In cell B4, why is there a space between the 'P' and the 'R' when there isn't in B2 and B3?

sorry ! should not be space between P and R should be like B2,B3

- In cell B41, why is there three spaces before the DA53?

also this is wrong should be one space , but when I use copy & paste forgot correcting it

- In cells B20 and B46, why are there hyphens when you said they should be deleted?

this is very complicated . I had to explain clearly . what I meant delete just locates before two digits and letters PR as in row 34,35 before 18PR
always delete before two digits and two letters.

Also, tell us about the two ** characters in rows 44 and 45

this is the symbol (stars) is existed in some tires sizes

 

[abdo meghari]

still isn't clear?

 

[Peter_SSs]

this is very complicated .

As you said ...

At the moment I do not see a clear set of logical rules that can be applied.

 

[abdo meghari]

@Peter_SSs maybe you have alternitive in your mind is close to my requirements. to understand the aim of make equel space among the letters and digits .

this report issue by differnt PC and I have report in my PC . I pull the values after matching between two reports by another macro . but the problem is different in the spaces among items between two reports and the macro will not pull the values because of thi problem . my real items about 1500 items if I do manually then it takes more time and exposed for mistakes like press twice or three spaces, but the macro correct any errors for spaces .

actually I don't complain , but maybe you can work for specific digits or lettres and ignore the others by split the macro for each specific digits or letters and issue new thread for each part for this complicated thread
thanks for your time .

 

[Peter_SSs]

@Peter_SSs maybe you have alternitive in your mind

No I don't because I do not understand the "rules" for how the desired format is arrived at. I think at least part of the problem is that it appears that English is not your main language so what is making sense to you is not making sense to me.

 

[tonyyy]

"...to understand the aim of make equel space among the letters and digits .

this report issue by differnt PC and I have report in my PC . I pull the values after matching between two reports by another macro . but the problem is different in the spaces among items between two reports..."

If the objective is to match items from the two reports, and the problem is the different placement of spaces, why not delete all the spaces from both reports and then run your matching macro?

Happy Holidays!

Tony