Normalized the data

iqbskh

New Member
Joined
Feb 28, 2011
Messages
4
Hi All,

I have below mentioned data and want the output as per details below

<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=421 border=0 x:str><COLGROUP><COL style="WIDTH: 188pt; mso-width-source: userset; mso-width-alt: 9179" width=251><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17>Cell A1 (Input)</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=170>Cell B1 (output)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17 x:str="SOUTHERN TITLE OF OHIO ">SOUTHERN TITLE OF OHIO </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Southern Title of Ohio</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17>148 E LIBERTY SUITE 120</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">148 E Liberty Suite 120,</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17 x:str="WOOSTER, OH 44691 ">WOOSTER, OH 44691 </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Wooster, OH 44691.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17>ATTN: ROD/JO ANN_______</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Rod/Jo Ann</TD></TR></TBODY></TABLE>

Regards
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Are we supposed to guess the rules that are to be applied to the data? Why don't you try using the PROPER function for a start, and then post back with the cases that aren't covered by that, and what rules you'd like to be applied to them.
 

iqbskh

New Member
Joined
Feb 28, 2011
Messages
4
Is there any nested forumla i can use with the proper function. The recoards are too many an manual doing this is not advisable.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Is there any nested forumla i can use with the proper function. The recoards are too many an manual doing this is not advisable.

We need to know what rules you want applied to your data. Perhaps not only providing the conditions, but also providing a better scope of your data (as well as desired outputs), will result in a response that fits your needs.
 

iqbskh

New Member
Joined
Feb 28, 2011
Messages
4
Please use this sheet for the condition to be appplied while writing formula.

<TABLE style="WIDTH: 792pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1055 border=0 x:str><COLGROUP><COL style="WIDTH: 188pt; mso-width-source: userset; mso-width-alt: 9179" width=251><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><COL style="WIDTH: 476pt; mso-width-source: userset; mso-width-alt: 23186" width=634><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17>Cell A1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=170>Cell B1</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 476pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=634>Condition</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17 x:str="SOUTHERN TITLE OF OHIO ">SOUTHERN TITLE OF OHIO </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Southern Title of Ohio</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 476pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=634>Whenever there is "OF" before and after the space. It should convert that word into small letters i.e "of"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17>148 E LIBERTY SUITE 120</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">148 E Liberty Suite 120,</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">After the end of the sentence, it should separate with ","</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17 x:str="WOOSTER, OH 44691 ">WOOSTER, OH 44691 </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Wooster, OH 44691.</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">I want U.S States in Capital letters i.e "OH", "MA", "CA", "TX" etc.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 188pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=251 height=17>ATTN: ROD/JO ANN_______</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Rod/Jo Ann</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Should above to remove text like "Attn:" and "__________" from the line</TD></TR></TBODY></TABLE>

Thanks
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
According to those rules, and making assumptions about the data, and treating all rows the same, this formula may do what you want:
Code:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(LEFT(A1,IF(ISERROR(FIND(",",A1)),999,FIND(",",A1)-1)))&IF(ISERROR(FIND(",",A1)),"",MID(A1,FIND(",",A1),999))," Of "," of "),"Attn: ",""),"_","")&","
 

Forum statistics

Threads
1,141,019
Messages
5,703,766
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top