Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default TTC delimiter restriction

Hi guys- pretty simple question I suppose for someone who knows their stuff.
I have a column of data which looks like this-

A1: ,08-5x1,,49
A2: ,08-5x1,,56
A3: ,10-10x1,,50
A4: ,10-10x3,,26
A5: ,10-14x1,,46
A6: ,10-19x1,,32

Etc.

As a result I need the second and third number to multiply together in a new cell.

So the answer to above would be
B1: 5 (5x1)
B2: 5 (5x1)
B3: 10 (10x1)
B4: 30 (10x3)
B5: 14 (14x1)
B6: 19 (19x1)

etc.

I have tried to use the text to columns function but this fails to help in two regards- when you use the fixed width to separate the multipliers out into new cells, it wont work because some are single and some double digits.
When I use a delimited TTC there are not enough delimiter options for me to separate out the data I need from everything else which is in there.

I can get the answer in a couple of steps without a problem, but I am hoping to have one formula which will do this.

What is the best way to do this?
Any advice?

Cheers,
Chris.
  #2   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by inequitude View Post
Hi guys- pretty simple question I suppose for someone who knows their stuff.
I have a column of data which looks like this-

A1: ,08-5x1,,49
A2: ,08-5x1,,56
A3: ,10-10x1,,50
A4: ,10-10x3,,26
A5: ,10-14x1,,46
A6: ,10-19x1,,32

Etc.

As a result I need the second and third number to multiply together in a new cell.

So the answer to above would be
B1: 5 (5x1)
B2: 5 (5x1)
B3: 10 (10x1)
B4: 30 (10x3)
B5: 14 (14x1)
B6: 19 (19x1)

etc.

I have tried to use the text to columns function but this fails to help in two regards- when you use the fixed width to separate the multipliers out into new cells, it wont work because some are single and some double digits.
When I use a delimited TTC there are not enough delimiter options for me to separate out the data I need from everything else which is in there.

I can get the answer in a couple of steps without a problem, but I am hoping to have one formula which will do this.

What is the best way to do this?
Any advice?

Cheers,
Chris.
=(MID(A1,FIND("-",A1)+1,FIND("x",A1)-FIND("-",A1)-1))*(MID(A1,FIND("x",A1)+1,FIND(",,",A1)-FIND("x",A1)-1))
__________________
Asobi Wa Owari Da
  #3   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by wickedchew View Post
=(MID(A1,FIND("-",A1)+1,FIND("x",A1)-FIND("-",A1)-1))*(MID(A1,FIND("x",A1)+1,FIND(",,",A1)-FIND("x",A1)-1))
Champion mate- worked a treat.
For future reference, if possible, could you explain above?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save-As Restriction Salman Excel Worksheet Functions 0 January 8th 07 06:26 AM
VLOOKUP with a restriction JackR Excel Discussion (Misc queries) 2 April 3rd 06 04:47 PM
Sheet tab Restriction harpscardiff Excel Discussion (Misc queries) 2 February 1st 06 02:57 PM
ask for overtime with time restriction Karl Excel Discussion (Misc queries) 0 November 3rd 05 11:32 PM
add restriction in Excel kalz Excel Worksheet Functions 3 December 9th 04 09:23 AM


All times are GMT +1. The time now is 06:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"