removing - from numbers

[rwab]

I have the following Data that I want to convert to the final data as shown below, notice that if the Data starts with 00-1- then the remaining numbers are the correct final data, if the Data starts with 06-2- then I want the -2- removed. These are the 2 scenarios I have in the data, is there a formula to convert this Data to Final Data, thanks in advance.

Data Final Data
00-1-09487-00007 0948700007
06-1-15776-01082 1577601082
00-1-18200-00117 1820000117
00-2-18200-00140 1820000140
00-1-70897-01331 7089701331
06-2-35985-06822 63598506822
07-2-19057-00156 71905700156
07-1-86150-00001 78615000001
07-2-86150-00014 78615000014
08-1-39728-00244 83972800244

[VBA Noob]

Your example and text don't match e.g

07-2-19057-00156.....71905700156
07-1-86150-00001.....78615000001
07-2-86150-00014.....78615000014
08-1-39728-00244.....83972800244

Start with 7 & 8

VBA Noob

[bigjaker]

Try this in Cell C1 and your information in Cell A1.

=IF(LEFT(A1,4)>"06-1",RIGHT(LEFT(A1,2),1) & LEFT(RIGHT(A1,11),5) & RIGHT(A1,5),LEFT(RIGHT(A1,11),5) & RIGHT(A1,5))

You should be able to copy it down and have it work ok. it is set up so that if the first four digits are greater then 06-1 then it returns the 6 or 7 or 8, leaves out the next digit and the two "-" and then all the remaining digits without the "-"'s.

Let me know if you need more explanation, or if I missed the mark.

Jake

[rwab]

Data.........................Final Data
00-1-09487-00007......0948700007
06-1-15776-01082 .....1577601082
00-1-18200-00117 .....1820000117
00-2-18200-00140 .....1820000140
00-1-70897-01331 .....7089701331
06-2-35985-06822 .....63598506822
07-2-19057-00156 .....71905700156
07-1-86150-00001 .....78615000001
07-2-86150-00014 .....78615000014
08-1-39728-00244 .....83972800244

Ok I see it all jumbled together when I posted, so I added as you did .......

Data Final Data Function
00-1-09487-00007............ 0948700007......... remove 1st 3 numbers
06-1-15776-01082............ 1577601082......... remove 1st 3 numbers
00-1-18200-00117............ 1820000117......... remove 1st 3 numbers
00-2-18200-00140............ 1820000140......... remove 1st 3 numbers
00-1-70897-01331............ 7089701331......... remove 1st 3 numbers
06-2-35985-06822............ 63598506822....... remove 1st and 3rd number
07-2-19057-00156............ 71905700156....... remove 1st and 3rd number
07-1-86150-00001............ 78615000001....... remove 1st and 3rd number
07-2-86150-00014............ 78615000014....... remove 1st and 3rd number
08-1-39728-00244............ 83972800244....... remove 1st and 3rd number

All Dashes are to be removed.


Also Jake your formula works for only removing the 1st 3 numbers.

Does this make sense now?

[bigjaker]

Rwab,

See my attached file. I believe I am dead on with my formula. It gives exactly what you are looking for in regards to results.

Jake

[rwab]

Yes, this worked perfectly, thanks for the help. When you have a free minute can explain what each part of the formula does, I think I have most of it figured out, but can't get all of it, again no rush and thanks again for the great formula.

[bigjaker]

Well it is a complex formula, I am guessing some one else probably has a much easier way but this worked.

=IF(LEFT(A1,4)>"06-1",RIGHT(LEFT(A1,2),1) & LEFT(RIGHT(A1,11),5) & RIGHT(A1,5),LEFT(RIGHT(A1,11),5) & RIGHT(A1,5))

The whole formula is an If then statement. The first part, LEFT(A1,4)>"06-1", looks at the 4 left most characters in cell A1 and if it is greater then 06-1 then it returns a true value. In the If then statement the next part is RIGHT(LEFT(A1,2),1) & LEFT(RIGHT(A1,11),5) & RIGHT(A1,5) and this is what is returned when the value is true. We need the second number of the first 2, so the Left pulls the left 2 numbers in cell A1, and then the Right runs afterwards returning the right digit of the 2 that the left just returned. This is just like normal math, inside () first, then outside (). To get rid of the "-" I did kind of the same thing using the same kind of set up. Since the numbers are alway the same amount of digits this type of setup works.

The & is used link together parts of the formula to return the strings of inforamtion needed. You can write sentances using this type of set up in a cell.

I have probably over explained this or maybe not. Basically the best way to learn what each part is doing is to break apart the formula in a test sheet.

If later on you don't want the formula to show you can use copy and paste special, then Values to past only the values that were returned from the formula.

Feel free to ask me any other questions you may have. I can explain better or more in depth if you don't understand a certain part.

Jake

[rwab]

I think I understand, however now I noticed that I have one set of Data with a 06-1 that needs to remove 1st and 3rd number, but the formula is set to remove 1st 3 numbers, can you fix?

00-1-09487-00007............ 0948700007......... remove 1st 3 numbers
06-1-15776-01082............ 1577601082......... remove 1st 3 numbers
00-1-18200-00117............ 1820000117......... remove 1st 3 numbers
00-2-18200-00140............ 1820000140......... remove 1st 3 numbers
00-1-70897-01331............ 7089701331......... remove 1st 3 numbers
06-2-35985-06822............ 63598506822....... remove 1st and 3rd number
07-2-19057-00156............ 71905700156....... remove 1st and 3rd number
07-1-86150-00001............ 78615000001....... remove 1st and 3rd number
07-2-86150-00014............ 78615000014....... remove 1st and 3rd number
08-1-39728-00244............ 83972800244....... remove 1st and 3rd number


06-1-35985-10062............ 63598510062....... remove 1st and 3rd number

[bigjaker]

I am sorry, I can't help you there. I will need more information as to why in one instance of 06-1 all 3 are gone and in another it is only the second number.

Jake

[rwab]

These are universal code numbers some are 10 digits some 11. I noticed the formula is > 06-1, if I change it to > 06-.1 then it works for the 06-1- numbers that required the 1st and 3rd number removed, but it then does not work for the 06-1- numbers that require the 1st 3 numbers removed.

See Below:

06-1-15776-01082............ 1577601082......... remove 1st 3 numbers
06-1-35985-10062............ 63598510062....... remove 1st and 3rd number

I have over 13,000 total numbers to convert, thanks for the help.

[bigjaker]

There are ways to change it, is it a case that the first digits will be 06-1-1 that need to be shorter, and 06-1-2 and above that need to be the longer number.

The formula only looks at the first 4 digits, what is the exact number that if it is greater then it become the longer format. If I had that information that would be much easier.

Jake

[rwab]

Attached is the spreadsheet with the formula you created that works for most, for the ones it does not work for I highlighted in yellow for you, thanks for the help.

[rwab]

What about an ARRAY formula?

[bigjaker]

Try this formula.

=IF(LEFT(A1,7)>"06-1-3",RIGHT(LEFT(A1,2),1) & LEFT(RIGHT(A1,11),5) & RIGHT(A1,5),LEFT(RIGHT(A1,11),5) & RIGHT(A1,5))

This one gets a little more specific on the 06-1 and is changed to 06-1-3. But if you have one that is 06-1-2 that needs the extra info then it won't work.

I am seeing you are not giving me enough information to help you completely. If you find that number 06-1-3XXXX-XXXXX is where you need to start to include the other information then that is what you need to change the 06-1-3 to. Then you will also need to change the first (LEFT(A1,7) as well. The 7 will need to be increased out to the number of characters that you looking at in the data column.

What you said you are looking for I have answered correctly. If you have more information you would like to share I can help you more, but what I really need is the data number where the change is made to needinig more info. If you find that there is a range that is one way and then a range that is another this information I need.

Jake

[bigjaker]

An Array will only work if I had enough information to write one. So far you are being to vague with what you are looking for.

Jake

[martindwilson]

=IF(OR(LEFT(SUBSTITUTE(A6,"-",""),4)={"0613","0623","0721","0718","0813"}),MID(SUBSTITUTE(A6,"-",""),2,1)&MID(SUBSTITUTE(A6,"-",""),4,255),MID(SUBSTITUTE(A6,"-",""),4,255))
just put any you want(without dashes) 1st and 3rd removed into

{"0613","0623","0721","0718","0813"} part of formula

[olasa]

06-1-4... deviates from the previous logic, in an unverified way:

for every lower number ... remove 1st 3 numbers
06-1-38489-00029 --> 63848900029 remove 1st and 3rd number
06-1-44381-10111 --> 4438110111 remove 1st 3 numbers
for every higher number ... remove 1 and 3rd number

//Ola

[rwab]

BigJaker, I attached the file for you on post #12 above.

[rwab]

Martin,

This worked great, thanks.

[rwab]

Martin,

When you have a free moment, could you explain the formula, I understand the part where you can insert other numbers "0613", "0623" and I went through the file and added the additional numbers, the formula worked perfectly, I was wonder what the other parts meant such as the 255.....

Thanks again for the help. You seem to be somewhat of a genius!

[ChemistB]

Hi Rwab,

If I understand correctly and you only want the first numbers if the mid number (between dashes) is not 1, then maybe this formula will work for you

Please Login or Register  to view this content.

The first part searches for the digit after the first dash and if it's less than 2, performs 1 thing and 2 or greater another.

If it's less than 2, then

Please Login or Register  to view this content.

basically taken the right 11 values, and removing the dashes.

If it's 2 or more, then

Please Login or Register  to view this content.

it concatenates the leftmost 2 digits with the rightmost 11 and removes dashes.

The +0 is necessary in each formula to change the value from a string (with leading zeros) to a number.

Is this what you need?

ChemistB

[martindwilson]

Please Login or Register  to view this content.

ok broken down

Please Login or Register  to view this content.

gets rid of all “ – “ returning say 123456789

Please Login or Register  to view this content.

gets first 4 characters characters from string above so 1234
Use this in the OR function to return true/false

Please Login or Register  to view this content.

would find 1234 in first 4 digits of 123456789 and return TRUE
Use this in an IF statement and then define which you want returned for each condition true/false)

Please Login or Register  to view this content.

now for the formula if true use

Please Login or Register  to view this content.

You wanted to delete ,in this case 1st and third digits so we need to get the 2nd and anything after the third

Please Login or Register  to view this content.

extracts the 2nd digit

Please Login or Register  to view this content.

extracts the rest of the digits starting from the 4th one
The 255 is the number of digits to return in effect use of 255 simply means everything!
Then simply use & to join strings together

For formula if false
You wanted to delete first 3 digits so re use the

Please Login or Register  to view this content.

part to return everything from 4th digit onwards

[rwab]

Thanks Martin, I understand now, the formula works perfectly.......