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," ","")

	Truck IDs	Formula 1	Formula 2	Problems
Sale	ACL123	ACL123		Letter in the Front
Purch	D*ACL123	ACL123		Letter in the Front
Sale	ABC 329	ABC 329	ABC329	Letter in the Front and SPACE - No Match
Purch	D*ABC329	ABC329	ABC329	Letter in the Front and SPACE - No Match
Sale	ABC 35060B	ABC 35060B	ABC35060B	Letter in the Front, SPACE and LETTER B - No Match
Purch	D*ABC35060	ABC35060	ABC35060	Letter in the Front, SPACE and LETTER B - No Match
Sale	PVHC20B	PVHC20B	PVHC20B	Letter in the Front and in the end - No Match
Purch	D*PVHC20	PVHC20	PVHC20	Letter in the Front and in the end - No Match
Sale	CC 01116	CC 01116	CC01116	Letter in the Front, SPACE and ZERO in between
Purch	D*CC1116	CC1116	CC1116	Letter in the Front, SPACE and ZERO in between
Sale	GOPV 036B	GOPV 036B	GOPV036B	Letter in the Front, SPACE, ZERO IN BETWEEN (need to remove B)
Purch	D*GOPV36B	GOPV36B	GOPV36B	Letter in the Front, SPACE, ZERO IN BETWEEN (need to remove B)
Sale	PVBL0099	PVBL0099	PVBL0099	Letter in the Front, Zeros in Between
Purch	D*PVBL99	PVBL99	PVBL99	Letter in the Front, Zeros in Between

<tbody>
</tbody>

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.