How to remove special character from string?

How do you output the character text to the right or left of a specific character string in Excel?

• Example: I have cells with addresses like: 123 Main Street 9th Floor or 123 Main Street Floor 9 I want a formula that will output the floor number. So left 5 characters before character string = "Floor" or everything to the right of Character string = "Floor" Thanks

• Answer:

  I understand that in your example you are using "Floor" as the determining value for what you want to use to break apart a string of data. And using the MID function would work under this particular instance. But what about those addresses where you may have "Room", "Bldg.", "Apt.", "Dept.", and other such Secondary Unit Designators?? You would need a specialized formula for each and every possible set of values. And that would be way too cumbersome. A better way is to have two distinct columns for storing the address values. One for the main number and street value, and the other for all of the secondary unit values. This is the correct way of handling such data. It is much easier to combine and/or merge multiple columns of data, than it is to separate data based upon various factors. And here are some examples of how to combine data: The first one is where one worksheet has different columns (A thru C) for a person's name: FirstName, MiddleName, LastName. In the second worksheet, the formula would look something like the following: = Sheet1! A1 & IF(ISBLANK(B1),""," " & B1) & IF(ISBLANK(C1),""," " & C1) This will combine all text values in multiple cells into a single value of a cell when entered in Sheet2. The same may be accomplished with the CONCATENATE function, which joins several text strings into one text string: = CONCATENATE(A1, " ", B1, " ", C1 ) In both formulas, the " " is used to add a single space between the two sets of strings being combined.