Formula Help Please

[SARABECK]

Hi Everyone,
I have truck ids that comes through the ledger with their appropriate sales and purchase dollars. The dollars should offset when a sale and purchase IDs are the same. However, the issues i'm running into is spaces in between, zeros in between, letters in the beginning and letters in the end for these truck IDs.

I use the 2 formulas below to help but it is not capturing everything.

Formula 1 - To remove all the D in front.

=IF(LEFT(B2,2)="D*",RIGHT(B2,LEN(B2)-2),IF(RIGHT(B2,1)="D",LEFT(B2,LEN(B2)-1),B2))

Formula 2 - To remove extra spaces in between.

=SUBSTITUTE(C8," ","")

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]Truck IDs[/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[TD]Problems[/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]ACL123[/TD]
[TD]ACL123[/TD]
[TD][/TD]
[TD]Letter in the Front[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*ACL123[/TD]
[TD]ACL123[/TD]
[TD][/TD]
[TD]Letter in the Front[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]ABC 329[/TD]
[TD]ABC 329[/TD]
[TD]ABC329[/TD]
[TD]Letter in the Front and SPACE - No Match[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*ABC329[/TD]
[TD]ABC329[/TD]
[TD]ABC329[/TD]
[TD]Letter in the Front and SPACE - No Match[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]ABC 35060B[/TD]
[TD]ABC 35060B[/TD]
[TD]ABC35060B[/TD]
[TD]Letter in the Front, SPACE and LETTER B - No Match[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*ABC35060[/TD]
[TD]ABC35060[/TD]
[TD]ABC35060[/TD]
[TD]Letter in the Front, SPACE and LETTER B - No Match[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]PVHC20B[/TD]
[TD]PVHC20B[/TD]
[TD]PVHC20B[/TD]
[TD]Letter in the Front and in the end - No Match[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*PVHC20[/TD]
[TD]PVHC20[/TD]
[TD]PVHC20[/TD]
[TD]Letter in the Front and in the end - No Match[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]CC 01116[/TD]
[TD]CC 01116[/TD]
[TD]CC01116[/TD]
[TD]Letter in the Front, SPACE and ZERO in between[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*CC1116[/TD]
[TD]CC1116[/TD]
[TD]CC1116[/TD]
[TD]Letter in the Front, SPACE and ZERO in between[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]GOPV 036B[/TD]
[TD]GOPV 036B[/TD]
[TD]GOPV036B[/TD]
[TD]Letter in the Front, SPACE, ZERO IN BETWEEN (need to remove B)[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*GOPV36B[/TD]
[TD]GOPV36B[/TD]
[TD]GOPV36B[/TD]
[TD]Letter in the Front, SPACE, ZERO IN BETWEEN (need to remove B)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]PVBL0099[/TD]
[TD]PVBL0099[/TD]
[TD]PVBL0099[/TD]
[TD]Letter in the Front, Zeros in Between[/TD]
[/TR]
[TR]
[TD]Purch[/TD]
[TD]D*PVBL99[/TD]
[TD]PVBL99[/TD]
[TD]PVBL99[/TD]
[TD]Letter in the Front, Zeros in Between[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you.

Please delete my other "Formula Needed" topic. I submitted this again as my original topic was not loading probably. My apologizes.

 

[Peter_SSs]

Peter_SSs, Thank you so much! Just awesome! thank you!!.

Glad it worked well for you.

I would like to apply this to my Rail IDs, the concept is the same except the Rail IDs starts with B in stead of D.

It's a guess without seeing some sample data, but you have definitely changed too many "D"s to "B"s. If this doesn't work, please post some representative data and expected results in 2 columns like my screen shot in post #9.

Function RailID(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "^(B\*)?(\D+)(0*)(\d+)(\D)?$"
    RailID = .Replace(Replace(s, " ", ""), "$2$4")
  End With
End Function

 

[SARABECK]

Thank you Peter_SSs. How come you didn't change all of the Ds to Bs?
Can you please add comments in the coding so i can use elsewhere too.
thank you so much.

 

[Peter_SSs]

Thank you Peter_SSs. How come you didn't change all of the Ds to Bs?
Can you please add comments in the coding so i can use elsewhere too.
thank you so much.

I can't add a lot by way of comments as there isn't actually much code in the function. Also, the function uses 'Regular Expressions' which can be quite complex to understand and any sort of introduction to Regular Expressions is beyond what could be expected in a forum like this.

Never-the-less I will try to give an outline of how this function, in particular its pattern, works.
Regular expressions look for a 'pattern', defined by the code, in a string.
In a RegEx pattern () divide the pattern into sections and certain characters have special meaning as well as their own (literal) meaning. To identify whether a character represents itself or a special meaning, that character can be preceded by a "\" or not.

The pattern in this RailID function works like this, after any spaces are first removed from the string.

^(B\*)?(\D+)(0*)(\d+)(\D)?$

^ represents the beginning of the string
followed by
(B\*)? the actual letter B followed by an asterisk. The "?" means that the preceding section may or may not occur (once) in the string
followed by
(\D+) \D represents a non-digit, "+" means one or more times
followed by
(0*) the digit zero, "*" means zero or more times.
followed by
(\d+) \d represents a digit, "+" means one or more times
followed by
(\D)? \D represents a non-digit, "?" means zero or 1 times
followed by
$ the end of the string

Finally, the .Replace(...., "$2$4") says that if the pattern is found, then return the 2nd and 4th sections. They are the red sections, being a series of 1 or more non-digits followed by a series of 1 or more digits.

Hopefully from that you can see why not all the "D"s were changed to "B"s. Most of them were preceded by a "\" giving their special meaning as either a non-digit or a digit.

 

[SARABECK]

Peter_SSs, Thank you so much for the explanation.

 

[Peter_SSs]

Peter_SSs, Thank you so much for the explanation.

You are welcome.