Number after Hyphen

[Lenard]

Hi,

I have a scenario where I have to ensure all Apartment unit numbers are consistent since they are referring to the same type with the maximum length 9 alphanumeric including 2 hyphens and the position of hyphen always located after the number e.g. C-07-02 or C5-07-02 or C5-13A-13
Further, alphabet "A" is always located after the number e.g. 13A or 03A

E.g. No1
Col A Result
C-7-02 ---------> C-07-02
C-07-2 ---------> C-07-02
C-7-2 ---------> C-07-02
C-07-02 ---------> C-07-02 (ok)
B3-7-10 ---------> B3-07-10
B3-11-5 ---------> B3-11-05
B3-11-05 --------> B3-11-05 (ok)
B3-11-15 --------> B3-11-15 (ok)

E.g. No2
Col A Result
A1-13A-8 --------> A1-13A-08
D-03A-6 --------> D-03A-06
D-3A-06 ---------> D-03A-06 (ok)
D-3A-6 ---------> D-03A-06
B5-13A-15 --------> B5-13A-15 (ok)

In this case, how to use excel formula or VBA to fix the following in order to achieve the above results :
1) the last 4 digits number after 1st hyphen to the left under example No1

07-02 for 7-02,07-2 and 7-2
07-10 for 7-10 1
11-05 for 11-5

2) the last 4 digits number with 1 alphabet "A" after 1st hyphen to the left under example No2

03A-06 for 03A-6, 3A-06 and 3A-6
13A-08 for 13A-8

Any help will be much appreciated

Thanks

Regards
Len

 

[Peter_SSs]

Len

This probably can be done with standard Excel formulas but I think they would be pretty complex. (Hopefully somebody will prove me wrong. )

Here is a user-defined function (UDF) that I think does what you want for both types of examples. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> sFormat(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "-(\d)(?=$|\D)"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    sFormat = RegEx.Replace(s, "-0$1")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Excel Workbook
	A	B
1	C-7-02	C-07-02
2	C-07-2	C-07-02
3	C-7-2	C-07-02
4	C-07-02	C-07-02
5	B3-7-10	B3-07-10
6	B3-11-5	B3-11-05
7	B3-11-05	B3-11-05
8	B3-11-15	B3-11-15
9
10
11	A1-13A-8	A1-13A-08
12	D-03A-6	D-03A-06
13	D-3A-06	D-03A-06
14	D-3A-6	D-03A-06
15	B5-13A-15	B5-13A-15
Format

 

[pgc01]

Hi

Another options for Peter's pattern:

            .Pattern = "-(\d)(?!\d)"

 

[Lenard]

Peter,

Appreciate your prompt reply
After using your UDF, it works perfectly
Thanks so much

Cheers
Len

 

[Richard Schollar]

Hi

Another options for Peter's pattern:

            .Pattern = "-(\d)(?!\d)"

Hi Pedro

Would you mind explaining what the ?! is doing here?

 

[Peter_SSs]

Hi Pedro

Would you mind explaining what the ?! is doing here?

I'll see how I go.

?! Means "negative lookahead".
So the final () is saying lookahead for 'not a digit'.

(pp 93-> in Ben Forta)

 

[Richard Schollar]

I'll see how I go.

?! Means "negative lookahead".
So the final () is saying lookahead for 'not a digit'.

(pp 93-> in Ben Forta)

Excellent - thanks Peter

My new 'guru' for regex!