Insert Single Character At Specific Point in Cell

[daedelous00]

Basically, I need to turn a column with data like this:

234019809 -- this is number 49 aof 1001
1009887347898 this is the serial number of a line toaster
the blue ottoman is fragile
122233302988 5 golden rings
10909888882 has three green feathers
789 drummers drumming

...into this:

234019809 | -- this is number 49 aof 1001
1009887347898 | this is the serial number of a line toaster
the blue ottoman is fragile
122233302988 | 5 golden rings
10909888882 | has three green feathers
789 drummers drumming

RULES
A pipe should be inserted after a number that meets the following criteria:

1) The number is 8-15 digits long, and
2) The number is at the beginning of the cell

Can anyone help me out with this? I can figure out most things in Excel on my own or with a bit of Googling but this one has me stumped.

Thanks!

[TheCman81]

Based on your post this will work for you.

Please see attached

Insert pipe.xlsx

[daedelous00]

Genius. Thanks!

[daedelous00]

Actually, I take that back. One issue.

The formula does not differentiate between numbers and letters. If the first word is between 8 and 15 characters, it will still insert a pipe. It should only do that for numbers.

[mellowmarshall]

Please Login or Register  to view this content.

counts up to the first space, captures that number of characters minus one (the space itself), multiplies it by 1 to convert text to number, and checks to see if it is indeed a number. then piggyback off the other poster's work.