Converting String to pairs.

  • Thread starter Thread starter UllSang
  • Start date Start date
U

UllSang

hello i have a file that was pre generated for me. it contains many
colums and rows.
a certian colum constains a string of 6 digits, like 272829 and 359514
and so on. I need those split up into pairs, either by comma or
something else so i can do a find and replace.
so it could look like 27,28,29 and 35,95,14. Any suggestions for how i
can automate it. I regularaly get theses lists and they contain 1200+
rows. it takes me hours. thanks for your help.
 
If A1 contains 123456 then =LEFT(A1,2) & "," & MID(A1,3,2) & "," & RIGHT(A1,2)

will display:
12,34,56
 
hello i have a file that was pre generated for me. it contains many
colums and rows.
a certian colum constains a string of 6 digits, like 272829 and 359514
and so on. I need those split up into pairs, either by comma or
something else so i can do a find and replace.
so it could look like 27,28,29 and 35,95,14. Any suggestions for how i
can automate it. I regularaly get theses lists and they contain 1200+
rows. it takes me hours. thanks for your help.
Click to expand...

Look up the functions len, left, right, and mid.

If there is no possibility of any number being other than six digits,
things are quite simple. You just put this in b1

=left(a1,2) & "," & mid(a1,3,2) & "," & right(a1,2)

and that splits it up. This assumes the first value is in cell a1,
and the rest following. Then just fill down.

If there is the possibility that some of those
numbers will be shorter than six digits, then it's a little more
complicated, and you would need to decide what to do with
those cases. Add zeros? Leave blanks? Something else?
So, for example, if a few of those numebrs were 5 digits,
but none less than 5, you need to decide if they become
01,56,23 or whatever, leading 0 or no leading 0. And you
need to decide if some are ever going to be 4 or less
digits, and what to do then.

If that happens, then you can do many things, some more
or less complicated. For example, if you wanted to have
leading 0's fill things in, you could make an intermediate
text-format column that does something like so.

left("000000",a1,6-len(a1)) & a1

This will then produce a string that is 6 chars long, with
0s at the front if the input number is less than 6. (But
note what happens if you ever get an input more than
6 chars long, namely BOOM!)
Socks
 
Back
Top