Insert multiple characters into string w LEFT RIGHT functions

**minnesotan** · 04-29-2014, 04:16 PM

I've got a long list of account numbers of varying length and ultimately need to add a dash and a dot into these numbers.

Here's an example

12345678 to> 1-2345.678
123456789 to> 12-3456.789
1234567890 to> 123-4567.890

The standard format is always 3 numbers after the . and 4 numbers between the - and .

I can get to this by doing a series of functions starting with this:
=LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,3) to get 12345.678 or 123456.789
copy/pasting that value into another field and then doing this
=LEFT(E1,LEN(E1)-8)&"-"&RIGHT(E1,8) to get the results above

I can't quite figure out the format to combine the multiple steps/functions into one so that I'm not copy/pasting values and re-doing the function.

I scoured the google and couldn't find something specifically related to this. Hope that was clear.

**TMS** · 04-29-2014, 04:35 PM

Formula:

Please Login or Register  to view this content.

Regards, TMS

**zbor** · 04-29-2014, 04:39 PM

Maybe this:

Formula:

Please Login or Register  to view this content.

**minnesotan** · 04-29-2014, 04:42 PM

TMS - That did it. Thanks for the speedy reply. I was pretty far from getting to that result during my tests.

zbor - using your formula I got #-#.###

**TMS** · 04-29-2014, 05:08 PM

You're welcome.

zbor's formula works just fine for me. It will, in fact, cope better with the shorter numbers ... I purposely cut off at 7 characters.

Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**TMS** · 04-29-2014, 05:12 PM

Example attached ...

**zbor** · 04-29-2014, 05:17 PM

Oh, didn't consider ending at 0.
Need to format it:

Formula:

Please Login or Register  to view this content.

**minnesotan** · 04-30-2014, 11:16 AM

That second suggestion did the trick zbor.

Thanks both of you for the suggestions!

Insert multiple characters into string w LEFT RIGHT functions

LinkBack

Thread Tools

Rate This Thread

Display

Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Re: Insert multiple characters into string w LEFT RIGHT functions

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Insert a zero on the left of a string?

[SOLVED] find word in string, count how many special characters are to its left

[SOLVED] Extract left characters from string with exception of 2 right characters

Insert a decimal point 2 characters to the left

removing 4 characters from the left of a string

Bookmarks

Bookmarks

Posting Permissions