Excel help - text string manipulation

Reply Subscribe

Thread Tools

04-13-2010, 08:23 AM

curls

Someone stole "My Garage"

Thread Starter

Join Date: May 2005

Location: Ottawa, Ontario

Age: 44

Posts: 3,538

Likes: 5

Received 17 Likes on 14 Posts

Excel help - text string manipulation

I'm trying to create a function without using macros, that will clean up text strings in a spreadsheet.

VARCHAR(30)
CHAR(4)
DECIMAL(15,3)

Those are 3 examples of the strings I wish to manipulate. What I want to have those look like is as follows:

VARCHAR____30__0
CHAR_______4____0
DECIMAL____15___3

So basically, if there is a right parenthesis at the end of the string, process whatever is between the parenthesis and place anything before a comma into its own cell, and if anything exists after a comma, place it in another cell. If there's no comma, put a zero in the cell in its place.

I think this can be done by reversing the string, counting length, etc... but can't figure it out for some reason. Can someone take a crack at this and let me know what the result is? The solution does NOT have to fit into one cell, so if needed, I can spread out the solution into multiple columns and have a "results" set of 3 columns at the end.

Thanks!

Reply Like

04-13-2010, 08:33 AM

hornyleprechaun

Bent = #1

Join Date: Dec 2001

Location: Marietta, GA

Age: 40

Posts: 13,473

Likes: 22

Received 25 Likes on 19 Posts

Give an example of the string...

Reply Like

04-13-2010, 08:47 AM

curls

Someone stole "My Garage"

Thread Starter

Join Date: May 2005

Location: Ottawa, Ontario

Age: 44

Posts: 3,538

Likes: 5

Received 17 Likes on 14 Posts

I did.

Examples:
VARCHAR(30)
CHAR(4)
DECIMAL(15,3)

Reply Like

04-13-2010, 09:04 AM

rza49311

Drifting

iTrader: (1)

Join Date: Feb 2006

Location: Southern VA

Age: 45

Posts: 3,072

Likes: 1

Received 8 Likes on 6 Posts

This will get the first column. No time to do the others at the moment.

=LEFT(A1,FIND("(",A1,1)-1)

Reply Like

04-13-2010, 09:17 AM

brizey

Burning Brakes

Join Date: Apr 2007

Location: DFW

Age: 54

Posts: 1,181

Likes: 0

Received 2 Likes on 2 Posts

Not sure if you can read this. It works. Result string is always 19 characters long.

Nevermind, that is too small. PM me your e-mail and I'll send it to you.

Reply Like

04-13-2010, 09:23 AM

brizey

Burning Brakes

Join Date: Apr 2007

Location: DFW

Age: 54

Posts: 1,181

Likes: 0

Received 2 Likes on 2 Posts

If anyone cares:

I basically used "find" to see if there was a comma, then "find" to get the position of the two parentheses. Then I used the "mid" function to pull out the numbers and base text. I used "len" to calculate how many underscores were needed and "rept" to build the underscore strings. Then "&" to concatenate it all together.

If you just want the numbers and base text, they are there as well in the calculation columns.

Last edited by brizey; 04-13-2010 at 09:26 AM.

Reply Like

04-13-2010, 09:27 AM

brizey

Burning Brakes

Join Date: Apr 2007

Location: DFW

Age: 54

Posts: 1,181

Likes: 0

Received 2 Likes on 2 Posts

<table style="border-collapse: collapse; width: 1240pt;" border="0" cellpadding="0" cellspacing="0" width="1652"><col style="width: 93pt;" width="124"> <col style="width: 192pt;" width="256"> <col style="width: 87pt;" span="2" width="116"> <col style="width: 372pt;" width="496"> <col style="width: 272pt;" width="362"> <col style="width: 137pt;" width="182"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 93pt;" height="20" width="124">input</td> <td class="xl65" style="border-left: medium none; width: 192pt;" width="256">comma location</td> <td class="xl65" style="border-left: medium none; width: 87pt;" width="116">( location</td> <td class="xl65" style="border-left: medium none; width: 87pt;" width="116">) location</td> <td class="xl65" style="border-left: medium none; width: 372pt;" width="496">first num</td> <td class="xl65" style="border-left: medium none; width: 272pt;" width="362">second num</td> <td class="xl65" style="border-left: medium none; width: 137pt;" width="182">base string</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">VARCHAR(30)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IFERROR(FIND(",",A3),FALSE)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND("(",A3)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND(")",A3)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B3),MID(A3,C3+1,D3-C3),MID(A3,C3+1,D3-C3-1))</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B3),MID(A3,B5+1,D5-B5-1),"0")</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT(A3,C3-1)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">CHAR(4)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IFERROR(FIND(",",A4),FALSE)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND("(",A4)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND(")",A4)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B4),MID(A4,C4+1,D4-C4),MID(A4,C4+1,D4-C4-1))</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B4),MID(A4,B6+1,D6-B6-1),"0")</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT(A4,C4-1)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">DECIMAL(15,3)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IFERROR(FIND(",",A5),FALSE)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND("(",A5)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND(")",A5)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B5),MID(A5,C5+1,D5-B5),MID(A5,C5+1,D5-C5-1))</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B5),MID(A5,B5+1,D5-B5-1),"0")</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT(A5,C5-1)</td> </tr> </tbody></table>
This shows all the formulas to just pull out the numbers and base text.

Reply Like

Trending Topics

KYB replacement shocks causing ride height issues on Type-S

30

481
End of the TLX?

20

880
2008 Acura TL 4th gear slip?

11

218
Oil Change

21

1.3k
2013 RDX Valve Adjustment Needed and When?? PCV??

6

125

04-13-2010, 09:33 AM

brizey

Burning Brakes

Join Date: Apr 2007

Location: DFW

Age: 54

Posts: 1,181

Likes: 0

Received 2 Likes on 2 Posts

Oops: Mistake in the fifth column:
<table style="border-collapse: collapse; width: 1300pt;" border="0" cellpadding="0" cellspacing="0" width="1732"><tbody><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; width: 153pt;" height="20" width="204">
</td><td class="xl65" style="border-left: medium none; width: 192pt;" width="256">
</td><td class="xl65" style="border-left: medium none; width: 87pt;" width="116">
</td><td class="xl65" style="border-left: medium none; width: 87pt;" width="116">
</td><td class="xl65" style="border-left: medium none; width: 372pt;" width="496">
</td><td class="xl65" style="border-left: medium none; width: 272pt;" width="362">
</td><td class="xl65" style="border-left: medium none; width: 137pt;" width="182">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">

See next post.
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>

Last edited by brizey; 04-13-2010 at 09:38 AM.

Reply Like

04-13-2010, 09:37 AM

brizey

Burning Brakes

Join Date: Apr 2007

Location: DFW

Age: 54

Posts: 1,181

Likes: 0

Received 2 Likes on 2 Posts

Dang:

Another mistake in the second number column.

Sorry for the jumbled posts. This one should work.

<table style="border-collapse: collapse; width: 1300pt;" border="0" cellpadding="0" cellspacing="0" width="1732"><col style="width: 153pt;" width="204"> <col style="width: 192pt;" width="256"> <col style="width: 87pt;" span="2" width="116"> <col style="width: 372pt;" width="496"> <col style="width: 272pt;" width="362"> <col style="width: 137pt;" width="182"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 153pt;" height="20" width="204">input</td> <td class="xl65" style="border-left: medium none; width: 192pt;" width="256">comma location</td> <td class="xl65" style="border-left: medium none; width: 87pt;" width="116">( location</td> <td class="xl65" style="border-left: medium none; width: 87pt;" width="116">) location</td> <td class="xl65" style="border-left: medium none; width: 372pt;" width="496">first num</td> <td class="xl65" style="border-left: medium none; width: 272pt;" width="362">second num</td> <td class="xl65" style="border-left: medium none; width: 137pt;" width="182">base string</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">VARCHAR(30)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IFERROR(FIND(",",A3),FALSE)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND("(",A3)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND(")",A3)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B3),MID(A3,C3+1,B3-C3-1),MID(A3,C3+1,D3-C3-1))</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B3),MID(A3,B3+1,D3-B3-1),"0")</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT(A3,C3-1)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">CHAR(4,5)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IFERROR(FIND(",",A4),FALSE)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND("(",A4)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND(")",A4)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B4),MID(A4,C4+1,B4-C4-1),MID(A4,C4+1,D4-C4-1))</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B4),MID(A4,B4+1,D4-B4-1),"0")</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT(A4,C4-1)</td></tr><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; border-top: medium none;" height="20">DECIMAL(0,35)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IFERROR(FIND(",",A5),FALSE)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND("(",A5)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=FIND(")",A5)</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B5),MID(A5,C5+1,B5-C5-1),MID(A5,C5+1,D5-C5-1))</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=IF(ISNUMBER(B5),MID(A5,B5+1,D5-B5-1),"0")</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">=LEFT(A5,C5-1)

</td> </tr> </tbody></table>

Last edited by brizey; 04-13-2010 at 09:42 AM.

Reply Like

04-13-2010, 09:43 AM

#10

curls

Someone stole "My Garage"

Thread Starter

Join Date: May 2005

Location: Ottawa, Ontario

Age: 44

Posts: 3,538

Likes: 5

Received 17 Likes on 14 Posts

ThHanks brizey -- I'll try to implement that and let you know how it goes!

I will also be adding a VLOOKUP as there's a slew of other values that wouldn't have user-defineable numbers within parenthesis... they have their own fixed set. That is easy though so don't worry about figuring that part out for me.