Splitting a multiplication formula

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Splitting a multiplication formula

Post by shreeram.maroo »

Hi I have a excel list which consists of two row - let's say invoice number and amount
Under amount column - figures are manually punched and muliplied by a certain factor - it could be 0.9, 0.8, 0.88 or so on, there are multiple varitieis there.

I want to split the amount and factor in a separate column. Is there any way i can do i quikcly for all 20000 rows ?


Regards
Shreeram

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Splitting a mutliplication formula

Post by shreeram.maroo »

Tried using textsplit. But * doesn't works, i guess it is itself a wildcard :p

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Splitting a mutliplication formula

Post by HansV »

Could you attach a small sample workbook that demonstrates the problem?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Splitting a multiplication formula

Post by shreeram.maroo »

Hi Hans,

Attaching a sample file
You do not have the required permissions to view the files attached to this post.

User avatar
rory
5StarLounger
Posts: 813
Joined: 24 Jan 2010, 15:56

Re: Splitting a multiplication formula

Post by rory »

You need to access the formula, so - in G4:

=LET(ft,FORMULATEXT(C4),t,RIGHT(ft,LEN(ft)-1),TEXTSPLIT(t,"*")+0)

and copy down.
Regards,
Rory

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Splitting a multiplication formula

Post by shreeram.maroo »

Thanks a ton Hans, this was really really helpful and saved lot of time.

User avatar
StuartR
Administrator
Posts: 12558
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Splitting a multiplication formula

Post by StuartR »

shreeram.maroo wrote:
16 Mar 2023, 11:02
Thanks a ton Hans, this was really really helpful and saved lot of time.
You might want to thank rory too
StuartR


shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Splitting a multiplication formula

Post by shreeram.maroo »

Apologies, I am not very much active here. Didn't notice the reply was from Rory. Thanks a lot Rory.. sorry for noticing it after 7 long months :(