1001001004006_001.tif to 001.001.001.004.006_001.tif

[Dbase Beginner]

Hi,

Anybody can help me out converting a list of these:
1001004003001_001.tif
1002006008002001_001.tif

to:
001.001.004.003.001_001.tif
001.002.006.008.002.001_001.tif

Thanks in advance

[VBA Noob]

Or try

=SUBSTITUTE("00"&SUBSTITUTE(A1,"00",".00"),"_.00","_00")

Assumes data in A1

VBA Noob

[Carim]

Hi,

My proposal is slightly different from VBA Noob's ...

Please Login or Register  to view this content.

HTH
Carim

[VBA Noob]

Carim,

Your's returns _.001

That's why I used the sec Subtitute

VBA Noob

[Carim]

VBA Noob,

Well with my old version of XL,
your formula returns 1.tif in the second case
whereas my formula returns correct answers in both instances ...
strange ... no ?

Cheers
Carim

[VBA Noob]

Very

Oh well. He should get one of them to work

Your return for me

001.001.004.003.001_.001.tif

My Double returns

001.001.004.003.001_001.tif



VBA Noob

[Carim]

VBA Noob,

Sorry tested your formula too quickly it does work fine ...
Would you accept ALL my apologies ...
I had not noticed the additional point .. I am getting old ...

Cheers
Carim

[Bryan Hessey]

Hi,

Anybody can help me out converting a list of these:
1001004003001_001.tif
1002006008002001_001.tif

to:
001.001.004.003.001_001.tif
001.002.006.008.002.001_001.tif

Thanks in advance

Hi,

The examples shown are 3 digit numbers with single digit values, in which case the formula will work to adjust.

However, once the double zero '00' is lost the formula may not work as expected, and the only solution short of VB code I could see is as per the attached, where: data in column A

In column F (untidy column selection)

=IF(MOD(FIND("_",A1)-1,3)=1,"00",IF(MOD(FIND("_",A1)-1,3)=2,"0",""))

in column G

=F1&LEFT(A1,FIND("_",A1)-1)

in column H

=MID(A1,FIND("_",A1),999)

in column I

=LEFT(G1,3)&IF(LEN(G1)>3,"."&MID(G1,3,3)&IF(LEN(G1)>6,"."&MID(G1,4,3)&IF(G1>9,"."&MID(G1,10,3)&IF(G1>12,"."&MID(G1,13,3)&IF(LEN(G1)>15,"."&MID(G1,16,3),""),""),""),""),"")&H1

your solution.

To explain, column F determines the first 'set' as 1, 2 or 3 digits ie, 1, 11 or 111

Column G adds F to the required portion of A

Column H has the trail

Column I puts a period (.) between each set of 3 and adds the trail.

Hope this helps
---