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
Splitting a multiplication formula
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Splitting a mutliplication formula
Tried using textsplit. But * doesn't works, i guess it is itself a wildcard :p
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting a mutliplication formula
Could you attach a small sample workbook that demonstrates the problem?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Splitting a multiplication formula
Hi Hans,
Attaching a sample file
Attaching a sample file
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Splitting a multiplication formula
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.
=LET(ft,FORMULATEXT(C4),t,RIGHT(ft,LEN(ft)-1),TEXTSPLIT(t,"*")+0)
and copy down.
Regards,
Rory
Rory
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Splitting a multiplication formula
Thanks a ton Hans, this was really really helpful and saved lot of time.
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Splitting a multiplication formula
You might want to thank rory tooshreeram.maroo wrote: ↑16 Mar 2023, 11:02Thanks a ton Hans, this was really really helpful and saved lot of time.
StuartR
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Splitting a multiplication formula
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 :(