Excel Formatting, Formatting formula

Hi! am using MS Excel 2007
===========================
1:

I have a column mentioning values:

Column 1:

Eight. Four
Seven. 0ne
Nine. Eight
Eight. Eight
Six. 0ne

I need results as :

Column 2:

8.4
7.1
9.8
8.8
6.1...
============================

2:

Problem.......
Column 1:

Nine Hundred Sixty Three Thou sand Seven Hu ndred Eighty One
Eight Hundred Sev enty Eight Thousand E ight Hundred Seventy Eight
Eight Hundred Twenty Two Thous and Seven Hundred Eighty Four
Eight Hund red Twenty Six Thousand One Hundred Eighty Nine
Nine Hundred Three Thousand Ni ne Hundred Six


Result
Column 2:

Nine Hundred Sixty Three Thousand Seven Hundred Eighty One
Eight Hundred Seventy Eight Thousand Eight Hundred Seventy Eight
Eight Hundred Twenty Two Thousand Seven Hundred Eighty Four
Eight Hundred Twenty Six Thousand One Hundred Eighty Nine
Nine Hundred Three Thousand Nine Hundred Six

i.e.: The result should take off all <spaces> followed by "small letters" ('Hu ndred' - 'Hundred' in first case)


I appreciate your valuable suggestions........ thank you!

For your first problem, try this:

Set up a table somewhere out of the way on your sheet like this:

Please Login or Register  to view this content.

Then with your data like:

Please Login or Register  to view this content.

In cell B1 enter the formula:

=VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:$AB$10,2,)

Note that it is a period and a space that separates the numbers, not just a period.


For the second part of your problem, the quickest is a UDF or Macro.

But before you can use these examples, you have to remove all the Space Characters in your string.
You can do this with a simple Search/Replace

Search for a Space
Replace with nothing

Then, there are several different examples here for inserting a space before a Capital Letter,
that should get you what your looking for.

http://www.mrexcel.com/forum/excel-q...al-letter.html

Here is another macro that may be easier to use:

http://excelribbon.tips.net/T012810_...l_Letters.html

Originally Posted by xenixman

For your first problem, try this:

Set up a table somewhere out of the way on your sheet like this:

Please Login or Register  to view this content.

Then with your data like:

Please Login or Register  to view this content.

In cell B1 enter the formula:

=VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:$AB$10,2,)

Note that it is a period and a space that separates the numbers, not just a period.


For the second part of your problem, the quickest is a UDF or Macro.

But before you can use these examples, you have to remove all the Space Characters in your string.
You can do this with a simple Search/Replace

Search for a Space
Replace with nothing

Then, there are several different examples here for inserting a space before a Capital Letter,
that should get you what your looking for.

http://www.mrexcel.com/forum/excel-q...al-letter.html

Hi!

I tried the formula with the FORLUMA :
B1: =VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:

But my result was :

Good except 2nd and 5th row which returned a NA

i.e.:

Eight. Four
Seven. 0ne
Nine. Eight
Eight. Eight
Six. 0ne


Result:

8.4
#N/A
9.8
8.8
#N/A

Thank you very much for the link!

Originally Posted by xenixman

For your first problem, try this:

Set up a table somewhere out of the way on your sheet like this:

Please Login or Register  to view this content.

Then with your data like:

Please Login or Register  to view this content.

In cell B1 enter the formula:

=VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:$AB$10,2,)

Note that it is a period and a space that separates the numbers, not just a period.


For the second part of your problem, the quickest is a UDF or Macro.

But before you can use these examples, you have to remove all the Space Characters in your string.
You can do this with a simple Search/Replace

Search for a Space
Replace with nothing

Then, there are several different examples here for inserting a space before a Capital Letter,
that should get you what your looking for.

http://www.mrexcel.com/forum/excel-q...al-letter.html

Hi!

Thank you for all the support,



I tried the formula :
B1: =VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:

But my result was :

Good except 2nd and 5th row which returned a NA

i.e.:

Eight. Four
Seven. 0ne
Nine. Eight
Eight. Eight
Six. 0ne


Result:

8.4
#N/A
9.8
8.8
#N/A

Originally Posted by xenixman

For your first problem, try this:

Set up a table somewhere out of the way on your sheet like this:

Please Login or Register  to view this content.

Then with your data like:

Please Login or Register  to view this content.

In cell B1 enter the formula:

=VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:$AB$10,2,)

Note that it is a period and a space that separates the numbers, not just a period.

Hi!

The second part of my prior problem was solved successfully! thanks for the same.

But for the first part i have received a negative for certain values:



I tried the formula :
B1: =VLOOKUP(LEFT(A1,FIND(". ",A1,1)-1),$AA$1:$AB$10,2,)&"."&VLOOKUP(RIGHT(A1,LEN(A1)-FIND(". ",A1)-1),$AA$1:

But my result was :

Good except 2nd and 5th row which returned a NA

i.e.:

Eight. Four
Seven. 0ne
Nine. Eight
Eight. Eight
Six. 0ne


Result:

8.4
#N/A
9.8
8.8
#N/A


Awaiting your early reply