# How To Insert Space In Different Categories Of Numbers

Jun 22, 2013
I have a column of numbers that look like this: 111222111222111 and I need them to look like this: 111 222 111 222 111. The numbers are all random and not 111222.

Then I have a second column with numbers like this: 11112222111122221111 but they need to be like this: 1111 2222 1111 2222 1111.

View 5 Replies
ADVERTISEMENT
Jan 10, 2013

I would like to insert a space after a series of numbers using VBA, ie:

A00000ABC = A00000 ABC

A111AAA = A111 AAA

View 9 Replies
View Related
Jun 19, 2013

I have a column of part numbers in the format 530020109 but I would like to change to 530 0201 09.

View 4 Replies
View Related
Oct 9, 2009

I've got a list of postcodes in one column and some of the postcodes do not have the correct amount of spaces between the characters.

So I'm looking to have a macro that will insert a space after each third character and then possible a separate macro to insert a space after each fourth character.

View 3 Replies
View Related
Jan 5, 2010

I'm trying to find a formula that will put a space in the correct place in a British postcode.

I have a column I need to sort this way. Some of the postcodes already have the correct space and others don't.

British postcodes can be in several formats......

n = a letter

1 = a number

n11 1nn

n1 1nn

n1 1n

n1n 1n

n1n 1nn

n11n 1n

n11n 1nn

nn1 1nn

nn1 1n

nn11 1nn

nn11 1n

On a side note I need to make all the text uppercase. In the column some already have uppercase and others don't.

View 14 Replies
View Related
Jun 24, 2012

I have hundreds of names in column D from D5 to D5000.

I need to insert 1 space before the start of the name.

E.g.

Now:

Smith

Result:

Smith

View 6 Replies
View Related
Dec 18, 2008

I have a column called "Unit Size," which contains data such as 200 g, 0.32 g, 15 mL, 2 mg.

I need a macro to automatically insert a space between the numbers and the letters, for example, if someone types 10g it automatically converts it to 10 g.

The strings may be of different lengths, i.e. contain different numbers of decimal places or signficant figures.

View 4 Replies
View Related
Aug 10, 2009

I'm trying to insert a 'space' after the second character/number in a cell.

I.e 123456 should become 12 3456

I have tried =LEFT(A1,2)&" " but this just deletes all data after the space. I've also tried =MID(A1,2)&" " but this makes excel very angry.....

View 8 Replies
View Related
Apr 12, 2014

I have list of phone numbers on excel.

I have A1 as xxxxxxxxxx(with out space)

I want same to be viewed in this format xx xxx xxx xx

View 5 Replies
View Related
Jun 30, 2014

I'm looking for a way to extract the data after the first space in a string to after the first set of numbers (always 8 digits). The formula I'm using is not accurate since the number of words that appears between the first space to the first set of numbers changes.

Sheet2ABC1Desired results2277 xxx spzrtmhk

companies 12345678 231516990 2475.06 3199.57 23.19 street 1 739.00xxx spzrtmhk

companies12345678 231516990xxx spzrtmhk companies 1234567831528 dts powertrain comp inc

15981808 231516989 43.55 84.40 48.40 5.00dts

[Code] ...........

View 9 Replies
View Related
Mar 5, 2010

I have a cell that contains a random number, spaces and other random numbers and at the end of this are words that name up a customer name.

Is there a formula that I can use that would just extract the letters only (i.e. the customer name)?

View 9 Replies
View Related
Jul 27, 2009

i have a list of customer codes each seperated by a space. In the column next to them i have a percentage. (see attached file before tab). I would like to be able to split the cell of customer codes and transpose them so that there is one cell per customer code with the appropriate % applied (please see attached file after example tab).

At the minute a manual process is completed of sorting in length order, using the text to column function then copy paste and transpose, then copy the % in. Its quite manual process, is there an easier way?

View 5 Replies
View Related
Aug 27, 2013

I have job names that look like this sample:

83369 CMT 2x Harpers cone links

I have a formula that copies just the first five numbers to a cell: =LEFT(B3,5)+0

Result is 83369

Now I need to copy everything BUT the first five numbers to another cell so the result is: CMT 2x Harpers cone links. How do I do that?

View 3 Replies
View Related
Oct 12, 2010

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1

2

3

4

8

9

becomes

1

2

3

4

8

9

View 7 Replies
View Related
Oct 16, 2012

Is there a way to extend the space of my userform beyond its maximum space? I have tried using vertical scroll bars but they were of no use.

View 1 Replies
View Related
May 26, 2007

I have been working on different formulas to return the text string between the first and last space and have been unsuccessful. Is this possible?

I have tried several combos or Left and Right, I have been able to get the values after the first space, and the values before the last space, but not between the spaces.

String: Y60

~C CULT NUCLEUS 3X2 SPRING WST BK XL

Desired results: D60

CULT NUCLEUS 3X2 SPRING WST BK

View 9 Replies
View Related
Feb 10, 2007

I have two words of differing character lengths separated by a space.

How can I remove the first word... essentially, all the charcters to the left of the space AND the space itself?

View 9 Replies
View Related
Feb 15, 2009

I have the following formula that works fine until someone uses the space bar to clear a cells contents

=COUNTA($D11:$AI11)

When the space bar is used to clear a cells contents the COUNTA statements includes the space in the count. How do I count the number of cells with content and exclude the space bar space in a cell?

View 4 Replies
View Related
Nov 6, 2012

If find dot. with out space in ( A1 ) cell remove space after dot in cell ( B1 ) Cell, vb or macro

A

B

M V Micunovic

MICUNOVIC,M V

L.T.Kudrjavceva

KUDRJAVCEVA,L. T.

D Sumarac m.l.

M. L,D SUMARAC

View 1 Replies
View Related
Apr 10, 2013

I only want to remove one space at the end of my text within a cell, if there is a space.

Code:

Sub hth()

Dim c As Range

For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp))

c.Value = Trim(c.Value)

Next c

End Sub

View 9 Replies
View Related
Jun 26, 2007

I'm using Excel 2007 and have a spreadsheet with 2500 rows. What I would like to do is insert the row number into a blank cell in each row. This will become a unique ID# for each row that I need to import this into a database later.

View 3 Replies
View Related
Mar 20, 2009

I need a formula to take a 6-Digit Number and change it into a 7-Digit Number. Probably a if statement would better because i have 7 digit numbers in the same column with 6-digit numbers. I need to insert zero between the 4th digit and the 5th digit of a 6-digit number. For Example:............

View 5 Replies
View Related
Jan 30, 2014

I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.

ABCD90bj10r7

ABCD,90,bj,10,r,7

ABCD90rt8r7

ABCD,90,rt,8,r,7

iFH15jr7ri12

iFH,15,jr,7,ri,12

iFG155jr8ri11

iFG,155,jr,8,ri,11

iFG15jr8ri9

iFG,15,jr,8,ri,9

View 2 Replies
View Related
Aug 24, 2007

I have a column that contains all telephone numbers without any spaces - 01234567890

I need to have at least one space inserted anywhere in the number i.e. 0 1234567890 or 01234 567890.

View 8 Replies
View Related
Jan 7, 2009

I have a string of numbers ex. 1234, 5678, 91011 in 400 rows. Before each string I would like to add 88.

View 4 Replies
View Related
Aug 3, 2006

I am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:

Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

5 -------Part E

6 -------Part F

As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

4

5 -------Part E

6 -------Part F

I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.

View 4 Replies
View Related
Feb 19, 2008

The excel file has industrial information arranged by sector. The sectors are represented by six digit codes that are not strictly sequential. For example, a typical example of consecutive codes is 120011,120012,120030,120040,130011. There are upwards of three hundred sectors.

The data I am using is missing certain sectors - sometimes more than 30 at a time. I would like to find a quick way to identify the missing sectors and insert rows there, preferably with the first cell in the row being the missing sectoral code.

I have a similar problems with columns as well. But I can always just transpose the data and use whatever solution works for the rows.

View 9 Replies
View Related
Apr 4, 2008

I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................

With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!

View 5 Replies
View Related
Oct 3, 2008

Need a function that would insert a letter or a number in front of numbers in a cell for example

column A

3245

I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with

Column A

S3245

View 3 Replies
View Related
Oct 7, 2009

I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.

My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator

View 3 Replies
View Related