Need VBA code to add trailing zeros to numbers within text

**adfo** · 08-14-2014, 11:36 AM

In my table, the entire field starting at (F7 through F??) contains a text that starts with a numeric code with "-" in between the number.
I need to scan all these records, and replace only the number part to be in the following format ##-### (that is 2 digits to the left of the "-" and 3 digits to the right of the "-", by adding trailing zeros to the left if needed. The remaining text would stay the same. All values need to be replaced in place (i.e. not copied to another field).

Example:

"9-50 ADJC CURD" Change to "09-050 ADJC CURD"
"3-110 PER SAM" Change to "03-110 PER SAM"
"11-70 REC" Change to "11-070 REC"
"17-120 QA" remains the same as "17-120 QA"

**Speshul** · 08-14-2014, 12:02 PM

Use 00-000 instead of ##-###

**Tsjallie** · 08-14-2014, 12:06 PM

This formula will do the tric:

Formula:

Please Login or Register  to view this content.

Cell A1 contains the original string.

**Solus Rankin** · 08-14-2014, 12:07 PM

Here an option with code

Please Login or Register  to view this content.

**Vikas_Gautam** · 08-14-2014, 12:11 PM

Got the solution...
remember copy the final result , and paste it as string..

Don't forget to click *

**Speshul** · 08-14-2014, 12:18 PM

Vikas, I'm not sure if the OP can use heper columns for this, "All values need to be replaced in place (i.e. not copied to another field)."

Although, you can compress all of those helper columns into a single cell;

=TEXT(LEFT(LEFT(B3,FIND(" ",B3)),FIND("-",LEFT(B3,FIND(" ",B3)))-1),"00")&"-"&TEXT(RIGHT(LEFT(B3,FIND(" ",B3)),LEN(LEFT(B3,FIND(" ",B3)))-FIND("-",LEFT(B3,FIND(" ",B3)))),"000")&" "&RIGHT(B3,LEN(B3)-FIND(" ",B3))

Or shortened to
=TEXT(LEFT(B3,FIND("-",B3)-1),"00")&TEXT(MID(B3,FIND("-",B3),FIND(" ",B3)-FIND("-",B3)),"000")&MID(B3,FIND(" ",B3),LEN(B3))

**stnkynts** · 08-14-2014, 12:21 PM

I guess I am slow on the draw. Here is my solution:

Please Login or Register  to view this content.

**Solus Rankin** · 08-14-2014, 12:32 PM

Sorry wrong thread.

**jindon** · 08-14-2014, 01:12 PM

Try this

Please Login or Register  to view this content.

**adfo** · 08-14-2014, 04:26 PM

Thanks All!

**Vikas_Gautam** · 08-14-2014, 09:58 PM

Sorry If I am wrong..
but I used helper columns for easy debugging later on..

Vikas Gautam

Need VBA code to add trailing zeros to numbers within text

LinkBack

Thread Tools

Rate This Thread

Display

Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Re: Need VBA code to add trailing zeros to numbers within text

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] count of numbers after decimal point with trailing zeros

Missing zeros (including all trailing and inbetween zeros)

Numbers as text dropping trailing decimals

Keep numbers as entered with trailing zeros

Keep Trailing Zeros When Convert To Text

Bookmarks

Bookmarks

Posting Permissions